Excel array formula examples for beginners and advanced users

Example 1. Count cells that meet certain condions

The introduction of SUMIF and COUNTIF functions as well as their plural counterparts in Excel 2007 made the use of array formulas superfluous in many scenarios. However, there are tasks where an array formula is the most effective, if not the only, solution.

Suppose you have 2 columns of numbers, column A (planned) and column B (actual). And you want to count how many times column B is greater than or equal to column A when a value in column B is greater than 0.

This task requires comparing two ranges and this is exactly what Excel array formulas are designed for.

So, you express the conditions as (B2:B10>=A2:A10) and (B2:B10>0), join them using the asterisk (*) that acts as the AND operator in array formulas, and include this expression in the SUM function's argument:

=SUM((B2:B10>=A2:A10) * (B2:B10>0))

Remember to press Ctrl + Shift + Enter to enter the Excel array formula correctly.

The next couple of paragraphs are intended for those who like looking under the hood. If you are not interested in tech details, you can skip right to the next formula example.

For better understanding of this formula in particular and Excel array formulas in general, let's select the two expressions within the SUM function's parentheses in the formula bar, and press F9 to view the arrays behind the formula parts. If you want more information on how the F9 key works, please check out Evaluating portions of an array formula with F9.

So, what we have here is two arrays of Boolean values, where TRUE equates to 1 and FALSE equates to 0. Since we are using the AND array operator (*) in the formula, SUM will add up only those rows that have TRUE (1) in both arrays, as shown in the screenshot below:

Example 2. Using several functions in Excel array formulas

Array formulas can work with several Excel functions at a time and perform multiple calculations within a single formula.

For example, if you have a table listing multiple product sales by several salesmen, and you want to know the maximum sale made by a given person for a given product, you can write an array formula based on the following pattern:

Assuming that the sales person names are in column A, product names are in column B and sales are in column C, the following formula returns the largest sale Mike has made for Apples:

=MAX(IF(($A$2:$A$9="mike") * ($B$2:$B$9="apples"), $C$2:$C$9,""))

Naturally, you can replace the names in the formula with cell references so that your users can simply type the names in certain cells without modifying your array formula:

In the screenshot above, the following Excel array formulas are used (don't forget to press Ctrl + Shift + Enter to enter them correctly):

Maximum: =MAX(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

Minimum: =MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

Average: =AVERAGE(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

Total: =SUM(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,""))

Example 3. Array formula to count all characters in a range

This array formula example shows how you can count the number of characters, including spaces, in a range of cells.

The formula is as simple as this:

You use the LEN function to return the length of the text string in each individual cell, and then you use the SUM function to add up those numbers.

For example, the array formula =SUM(LEN(A1:A10)) calculates the total number of all chars with spaces in range A1:A10.

Example 4. Array formula to count specific character(s) in a range

If you want to know how many times a given character or a group of characters appears in a specified range of cells, an array formula with the LEN function can help again. In this case, the formula is a bit more complex:

And here is a practical example. Suppose, you have a list of orders where one cell may contain several order numbers separated by commas or any other delimiter. There are several order types and each has its own unique identifier - the first character in an order number.

Assuming that orders are in cells B2:B5 and the unique identifier in E1, the formula is as follows:

=SUM((LEN(B2:B5) - LEN(SUBSTITUTE(B2:B5, E1, ""))) / LEN(E1))

At the heart of this formula, the SUBSTITUTE function replaces all occurrences of the specified character with an empty string ("").

The substituted string is fed to the LEN function to get the string length without the character of interest ("K" in this example). And then, you subtract the length of the substituted string from the original string's length. The result of this operation is an array of character counts, one per cell, which you divide by the substring length. The operation of division is not strictly necessary when you count a single character like in this example. But if you are counting the occurrences of a specific substring in a range (e.g. orders beginning with "KM"), you do need to divide by the substring length, otherwise each character in the substring will be counted individually.

Finally, you use SUM to add up the individual counts.

Example 5. Sum values in every other or N^th row

If you want to sum every other or every N^th row in a table, you will need the SUM and MOD functions combined in an array formula:

The MOD function returns the remainder rounded to the nearest integer after the number is divided by the divisor. We embed the ROW function into it to get the row's number, and then divide it by the N^th row (e.g. by 2 to sum every second cell) and check if the remainder is zero. If it is, then the cell is summed.

The double unary operator (--) is used to convert non-numeric Boolean values TRUE and FALSE returned by MOD into 1 and 0 for the SUM function to be able to add up the numbers.

For example, to count every other cell in range B2:B10, you use one of the following formulas:

Count even rows (2^nd, 4^th, etc.):
=SUM((--(MOD(ROW($B2:B10), 2)=0))*(B2:B10))

Count odd rows (1^st, 3^rd, etc.):
=SUM((--(MOD(ROW($B2:B10), 2)=1))*(B2:B10))

To get a universal formula that can sum values in any N^th rows that you specify and work correctly with any ranges regardless of their location in a worksheet, the formula has to be improved a little further:

=SUM((--(MOD((ROW($B$2:$B$7)-ROW($B$2)), E1)=E1-1))*($B$2:$B$7))

Where E1 is every N row you want to sum.

Example 6. Perform different calculations on numbers within different ranges

Here is a typical situation for many vendors - the unit price varies depending on the purchased quantity, and your goal is to write a formula that calculates the total price for any amount input in a specific cell.

This task can be easily accomplished by using the following nested IF formula:

=B8*IF(B8>=101,B6, IF(B8>=50, B5, IF(B8>=20, B4, IF( B8>=11, B3, IF(B8>=1, B2, "")))))

However, this approach has a significant limitation. Because the formula references each price in cells B2 to B6 individually, you will have to update the formula as soon as your users change any of the existing ranges or add a new quantity range.

To make the formula more flexible, operate on arrays rather than individual cells. In this case, no matter how many values are changed, added or deleted, you will only have to update a single range reference in the formula.

=SUM(B8*(B2:B6) * (--(B8>=VALUE(LEFT(A2:A6,FIND(" ",A2:A6))))) * (--(B8<=VALUE(RIGHT(A2:A6,LEN(A2:A6) - FIND(" to ",A2:A6)-LEN(" to" ))))))

The detailed break down of this formula will probably require a separate article, so I will make just a quick overview of the logic. If you select individual parts of the formula in the formula bar and press F9, you will see that it boils down to evaluating the following 3 arrays (provided the quantity in B8 is 100 like in the screenshot above):

=SUM(B8*{20;18;16;13;12}*{1;1;1;1;0}*{0;0;0;1;1})

The first 5-element array is nothing else than the price numbers in cells B2:B6. And the last 2 arrays of 0's and 1's determine which price will be used in calculation. So, the main question is - where do these two arrays come from and what do they mean?

The formula includes 2 VALUE functions: (B8>=VALUE())*(B8<=VALUE())

The 1^st function checks if the value in B8 is greater than or equal to the lower bound of each "unit quantity" range, and the 2^nd one checks if B8 is less than or equal to the upper bound of each range (the combinations of LEFT, RIGHT, FIND and LEN functions are used to extract the upper and lower bound values). As a result, you get 0 if the condition is not met, and 1 if the condition is met.

Finally, the SUM function multiplies the quantity in B8 by each element of the price array (B2:B6) and by each elements of the 0's and 1's arrays. Since multiplying by 0 always gives 0, only one price is used in the final calculation - the element that has 1's in the last two arrays.

In this example, the quantity is multiplied by $13 that corresponds to "50 to 100" amount range. It is the 4^th item of the price array (cell B5 in range B2:B6), and it is the only element that has 1's in the last two arrays.

For the formula to work correctly, be sure to check these two things:

• The quantities in A2:A6 should constitute a contiguous range such that no value is left out.
• All quantities in A2:A6 have to be entered in this specific pattern "X to Y" because it is hard-coded in the formula. If your quantities are entered in a different way, say "1 - 10", then replace " to" with " -" in the formula.

If you want to display an "Out of range" message when the quantity input in B8 is outside the amount range, include the following IF statement:

=IF(AND(B8>=VALUE(LEFT(A2,FIND(" ",A2))), B8<=VALUE(RIGHT(A6,LEN(A6) - FIND(" to ",A6)-LEN(" to" )))), SUM(…))

This complex If function does a very simple thing - checks if the value in B8 is greater than or equal to the lower bound in A2 and less than or equal to the upper bound in A6. In other words, it tests this condition: AND(B8>=1, B8<=200).

The complete formula goes as follows:

=IF(AND(B8>=VALUE(LEFT(A2,FIND(" ",A2))), B8<=VALUE(RIGHT(A6,LEN(A6)-FIND(" to ",A6)-LEN(" to" )))), SUM(B8*(B2:B6)*(--(B8>=VALUE(LEFT(A2:A6,FIND(" ",A2:A6)))))*(--(B8<=VALUE(RIGHT(A2:A6,LEN(A2:A6)-FIND(" to ",A2:A6)-LEN(" to" )))))), "Out of range")