The Power of Excel Array Formulas Part I

You may be familiar with the IF function in Excel, which lets a cell display one of two possible values, depending on whether a condition is true or false. The syntax is:

=IF(condition to test, value if the condition is true, value if the condition is false)

Note that whenever a function has multiple arguments, you separate them with commas.

For example, let’s say if the value of B5 is less than 100, you want B6 to have a value of 6%, and if B5 has a value that’s 100 or higher, you want B6 to have a value of 8%. You would enter this into B6:

=IF(B5<100,6%,8%)

That’s a great feature, but what if you want to test for two conditions? For example, let’s say you’re selling different types of fruit to customers in multiple states, and you want to add only the sales of apples to New Jersey, but not apples to New York, or bananas to New Jersey. The IF function can’t do that, but an Excel array formula can.

With an array formula, rather than calculating values of individual cells, you calculate the values of multiple cells (a range) at once. What’s also different about array formulas is that you must enter them by pressing Ctrl + Shift + Enter, rather than just the Enter key, by itself.

Here is a list of orders we received. We’ll put the array formula in D17.

Type this into D17 (not case-sensitive):

=SUM((D4:D15)*(B4:B15="apples")*(C4:C15="new jersey"))

This formula tells Excel to use the SUM function on column D, but only in the rows where columns B and C meet your criteria for fruit and state. Since the criteria are text and not numbers, you need to put them in quotes.

After typing the formula, make sure to press Ctrl + Shift + Enter, or Excel will throw a #VALUE error.

The result in D17 should be 5459, since it doesn’t include order #118 or #122, which are for different states. Also look at the formula bar:

Excel appended curly braces to the beginning and end. That’s how you know it’s an array formula. If you’re a good typist, resist the temptation to type the braces yourself. It won’t work.

Think you got it? On your own, go to D18 and calculate the sales of bananas to Pennsylvania. Download multiple Array Formula 1_criteria.xlsx for a head start.