The Power of Excel Array Formulas Part II

In a previous post, I showed how you could use an Excel array formula to simulate an IF function that has two conditions. But arrays can create other types of what-if analysis, too.

You might have guessed or already known that the SUM function is used to add numbers and the AVERAGE function calculates an average. The SUMIF function differs in that it will add numbers only if a condition is met while the AVERAGEIF function will calculate an average only if a condition is met.,.,

Using the example from my previous post, let’s say you’re a fruit wholesaler who is expanding into three new states. Below is a list of orders. While the SUM function will give you the total of all orders from all states, here’s how SUMIF will let you, uh, cherry-pick just the orders from New Jersey. Perhaps Jersey has a competitive market and you need to keep track of it.

The syntax is:

=SUMIF(range that has condition to test, the condition itself, the range of numbers to add)

In D18, the formula is (not case-sensitive):

=SUMIF(C4:C15,"new jersey",D4:D15)

Note that since the condition is text and not a number, it has to be in quotes.

If you want to find the average of all sales to New Jersey, just replace SUMIF with AVERAGEIF.

But what if you want to find the highest or lowest sale to New Jersey? Excel doesn’t have a “MAXIF” or “MINIF” function. That’s where an array formula comes to the rescue.

In D19, we’ll run the MAX function with the IF function nested in it, and enter the formula as an array. Type this formula:

=MAX(IF(C4:C15="new jersey",D4:D15))

TIP: When you nest one function inside another, only the outer-most function gets an equal sign before it.  Make sure to press Ctrl + Shift + Enter. If you press Enter by itself, Excel will throw a #VALUE error.

You have created a formula that checks column C for New Jersey and add the number next to it in column D. Also notice that this IF function has a condition parameter and a True parameter, but no False parameter. It isn’t necessary, because it’s just a zero value. But if it’s easier to understand, you can get the same result if you change the formula to:

=MAX(IF(C4:C15="new jersey",D4:D15,0))

Now it’s your turn: in D20, find the lowest sale to New York. Use Array Formula 2_Download if.xlsx to get a head start.