What Is An Array Formula?

Introduction

What is an array formula anyway?

In simple terms, an array formula is a formula that works with an array of values, rather than a single value. Array formulas can return a single result or multiple results.

That sounds simple enough, and indeed many array formulas are not complex. However, because some array formulas need to be entered in a special way, and some don’t , array formulas live mostly in the geeky realm of super users.

In fact, in the world of Excel formulas, the term “array formula” may be responsible for more confusion than just about any other concept.

With the introduction of Dynamic Arrays in Excel 365 , array formulas are going to become a lot more common, because they are now much easier to use and understand:

1. No need for control + shift + enter
2. Formulas that return multiple results will spill

Related videos

We’ve been working on a new course, Dynamic Array formulas , and these videos help explain the topics discussed below:

• What is an array formula?
• 3 basic array formula examples

Basic array formula example

In the example below, we want to find the maximum change in temperature over seven days:

The formula in F5 is:

=MAX(C5:C11-D5:D11)

This is an array formula that returns a single result.

Working from the inside out, we first subtract the low temps from high temps:

C5:C11-D5:D11 // array operation

Each range contains 7 values, which we can expand into arrays like this:

{86;84;89;87;82;85;88}-{69;65;57;62;70;59;59}

This is called an array operation . We are working with multiple values, and the result after subtraction is a new array with 7 values, where each value represents the change in temperature on the given day:

{17;19;32;25;12;26;29} // new array

The new array is returned directly to the MAX function which returns the largest value:

=MAX({17;19;32;25;12;26;29}) // returns 32

You can see that this array formula is actually quite simple!

Traditional Excel - complication and danger

The problem arises when we enter the formula. In “Traditional Excel” (currently, every version of Excel except Office 365), this formula must be entered with control + shift + enter . When entered this way, Excel will display curly braces in the formula bar like this:

{=MAX(C5:C11-D5:D11)}

These curly braces tell you that Excel is handling the formula as an array formula. In other words, Excel is “letting you” work with multiple values.

To most users, that’s pretty strange and confusing. But it gets worse.

If you (or someone else) forget to enter the formula with control + shift + enter, the same exact formula may return an incorrect result.

For example, the formula above without control + shift + enter will return 17, the change in temperature on Monday. This will be a “silent failure” – no warning will occur. The formula will simply stop working correctly.

Obviously, formulas that return incorrect results are bad news :)

Dynamic Excel - simplicity and clarity

The great thing about the Dynamic Array version of Excel , is that array formulas just work . You don’t have to use control + shift + enter with any array formula.

Even better, a formula that returns multiple values will spill these values onto the worksheet. This makes array formulas much easier to understand because it’s obvious when a formula is returning more than one value.

In contrast, the same formulas in previous versions of Excel will display only one result in a single cell, no matter how many values are actually returned.

The bottom line is that working with array formulas in Excel is now easier and more intuitive than ever. You can now use array formulas whenever you like, without worrying about fancy syntax requirements.

Videos

What is an array formula? 3 basic array formulas

There are eight functions in Excel that work differently than you might realize. I call these “range-based conditional functions” or RACON (Ray-con) functions for short, because these functions apply criteria to a range and return a single result based on supplied criteria. In other words, these functions perform range-based , conditional aggregation. Here is the list of RACON functions:

Click any function above for details and many examples.

Other Excel MVPs I know refer to this same group of functions as the *IFS functions.

What’s different?

Although these functions are widely used, they operate differently in three key ways you may not have noticed:

1. Logical expressions are split into two parts
2. Ranges are required , you can’t substitute arrays
3. Other miscellaneous quirks

In the article below, I explain these differences in some detail.

Once you understand how these functions work, you can more easily get them to do what you want. In addition, you’ll have a better idea about when you should explore alternatives.

Note: although the examples below deal exclusively with the COUNTIFS function, the same differences apply to all functions in the table above.

Logical expressions are split

A key difference in these functions versus others is that they apply criteria in a special way, by separating the logical expressions into two parts: range and criteria. For example, the basic syntax for COUNTIFS looks like this:

=COUNTIFS(range,criteria,range,criteria,etc)

I assume this was done for ease and convenience, to somehow make it “easier” to enter formula criteria without really understanding how to enter criteria as an expression, but it has consequences. For example, let’s say you want to test the value in A1 and return TRUE when the value is greater than 5. With the IF function the syntax is simple:

=IF(A1>5,TRUE)

Nothing special, right? The logical expression, A1>5, makes perfect sense, and we can copy it down the column to test all values:

Note: I am embedding the expression A1>5 in the IF function only to illustrate how a logical expression would typically appear inside another function. The expression by itself (i.e. =A1>5), will create exactly the same TRUE/FALSE result.

Now, let’s say you want to count values in A1:A10 greater than 5 with the COUNTIFS function . This requires the following formula:

=COUNTIFS(A1:A10,">5")

Notice anything strange about this formula? What’s that text (">5") doing in there?

Isn’t 5 a numeric value? Yes, indeed.

This happens because the logical expression A1:A10>5 has been split into two parts. The range A1:A10 is perfectly valid, so it remains unquoted, but >5 is a partial and invalid expression. As a result, it must appear in quotes like “>5”.

Somewhere behind the scenes, the Excel formula engine reassembles the text into a valid expression again:

A1:A10>5 // reassembled expression

Things get even weirder when we involve another cell in the criteria. For example, let’s say we want to check values greater than B1. With the IF function, this is straightforward:

=IF(A1>B1,TRUE) // simple

But let’s say we want to count values in A1:A10 that are greater than B1. Now we need to write:

=COUNTIFS(A1:A10,">"&B1) // what the ??

We are counting numeric values, but we need to use both concatenation and quoted text? Yes.

But note the cell reference itself is not quoted :)

What about text values? Suppose we want to check if a cell equals “apple”. With IF, the logic is simple:

=IF(A1="apple",TRUE)

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/IF%20example%20-%20A1%20equals%20apple.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“IF example - if A1 equals “apple” - 5”>

Now, what if we want to count cells equal to “apple”?

Both of these formulas will work:

=COUNTIFS(A1:A10,"=apple")
=COUNTIFS(A1:A10,"apple")

Basically, the “equals to” (=) operator is implied, so it’s not required.

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/articles/inline/COUNTIFS%20example%20-%20count%20cells%20equal%20to%20apple.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“COUNTIFS example - count cells equal to “apple” - 6”>

So, the logic is simpler, but quirky. In a similar way, you can count values equal to 5, with either:

=COUNTIFS(A1:A10,5) // no text
=COUNTIFS(A1:A10,"=5") // text

As above, both will work.

To summarize, because RACON functions split logical expressions into two parts, they require a unique syntax:

1. Criteria that include logical operators must be enclosed in double quotes (””)
2. The equals to (=) operator can be omitted (or not)
3. Criteria based on another cell must use concatenation

Let’s look next at the second key difference, ranges.

Ranges are required

A second key difference with RACON functions is range arguments. When a RACON function asks for a range, you must provide a range — you can’t provide an in-memory array*.

This may not seem like a big deal at first. After all, the data is on the worksheet, right? So why not supply a range? However, in real-life situations, this requirement has consequences.

One example is dates. Let’s say you want to check dates in A1:A10 to see which ones are in June?

With the IF function, you can simply use the MONTH function like this:

=IF(MONTH(A1)=6,TRUE) // month is 6?

The logic is simple: extract the month number with the MONTH function and compare the result to 6. If I put the formula in B1 and copy it down to B10, we’ll get TRUE for dates in the month of June, and FALSE for all other dates:

By the way, this is an example of nesting one formula inside another.

Now, how can we count the dates in A1:A10 that are in June? You might think we could use the MONTH function like we did with IF:

=COUNTIFS(MONTH(A1:A10),6) // nope

Nope. While this looks perfectly reasonable, it isn’t going to work. Excel won’t even let you enter the formula. Instead, it will throw a generic “There’s a problem with this formula” error. In fact, the “problem” is that you must supply a range as the first argument to COUNTIFS.

Even though the MONTH function will happily give you all 10 month numbers in an array, an array won’t cut it. You must supply a range.

Okay, so how can you get COUNTIFS to count dates in June? It’s not pretty:

=COUNTIFS(A1:A10,">="&DATE(2020,6,1),A1:A10,"<="&DATE(2020,6,30))

We are basically using the DATE function to hardcode start and end dates into the formula and using multiple criteria. The first condition tests for dates greater than or equal to June 1, the second condition tests for dates less than or equal to June 30.

If we put the date “1-June-2020” into another cell, B1, we can make things slightly less painful by using the EOMONTH function to calculate the last day in June:

=COUNTIFS(A1:A10,">="&B1,A1:A10,"<="&EOMONTH(B1,0))

But we still can’t avoid the monkey business of concatenation. And, I should note, both of the COUNTIFS examples above are testing for dates in June 2020 , not just June.

The problem in a nutshell is that because COUNTIFS requires a range, we lose the power to manipulate values in that range directly.

• To be clear, spill ranges work fine in RACON functions, since they are ranges on the worksheet.

Other quirks

RACON functions also have some other quirks, including:

1. They strip leading zeros in text-based numbers
2. They evaluate certain logical expressions differently
3. They evaluate very long numbers differently
4. 3D references are not allowed for range arguments

Alternatives

Now, how can we count dates in June with a function that doesn’t have these limitations? Well, before the Dynamic Array version of Excel arrived, SUMPRODUCT would be the most streamlined solution:

=SUMPRODUCT(--(MONTH(A1:A10)=6))

This is an array formula , but it does not require control + shift + enter.

In the Dynamic Array version of Excel, we can use the SUM function in the same way:

=SUM(--(MONTH(A1:A10)=6))

This does require control + shift + enter in earlier versions of Excel, but in Dynamic Excel, it will just work.

Both formulas use the double-negative trick to change TRUE FALSE values to 1’s and 0’s, so they can be counted.

We can also use the FILTER function like this:

=COUNT(FILTER(A1:A10,MONTH(A1:A10)=6))

This involves one more function, but notice the logic used inside FILTER to test for month is exactly the same .

Consistency and the future

The beauty of the new functions in Excel is that they all use logical expressions in the same consistent way . Even better, dynamic arrays let you build simple array formulas with the same clean and simple syntax, no fancy keystrokes required.

Although the RACON functions are still useful and, in some slightly twisted way, “easier”, many future Excel formulas will be simpler, more consistent, and easier to learn.