Put advanced array formulas to work
This section provides examples of advanced array formulas.
Sum a range that contains error values
The SUM function in Excel does not work when you try to sum a range that contains an error value, such as #N/A. This example shows you how to sum the values in a range named Data that contains errors.
The formula creates a new array that contains the original values minus any error values. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns a specific value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. In this case, it returns empty strings ("") for all error values because they evaluate to TRUE, and it returns the remaining values from the range (Data) because they evaluate to FALSE, meaning that they don't contain error values. The SUM function then calculates the total for the filtered array.
Count the number of error values in a range
This example is similar to the previous formula, but it returns the number of error values in a range named Data instead of filtering them out:
This formula creates an array that contains the value 1 for the cells that contain errors and the value 0 for the cells that don't contain errors. You can simplify the formula and achieve the same result by removing the third argument for the IF function, like this:
If you don't specify the argument, the IF function returns FALSE if a cell does not contain an error value. You can simplify the formula even more:
This version works because TRUE*1=1 and FALSE*1=0.
Sum values based on conditions
You might need to sum values based on conditions. For example, this array formula sums just the positive integers in a range named Sales:
The IF function creates an array of positive values and false values. The SUM function essentially ignores the false values because 0+0=0. The cell range that you use in this formula can consist of any number of rows and columns.
You can also sum values that meet more than one condition. For example, this array formula calculates values greater than 0 and less than or equal to 5:
Keep in mind that this formula returns an error if the range contains one or more non-numeric cells.
You can also create array formulas that use a type of OR condition. For example, you can sum values that are less than 5 and greater than 15:
The IF function finds all values smaller than 5 and greater than 15 and then passes those values to the SUM function.
You can't use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.
Compute an average that excludes zeros
This example shows you how to remove zeros from a range when you need to average the values in that range. The formula uses a data range named Sales:
The IF function creates an array of values that do not equal 0 and then passes those values to the AVERAGE function.
Count the number of differences between two ranges of cells
This array formula compares the values in two ranges of cells named MyData and YourData and returns the number of differences between the two. If the contents of the two ranges are identical, the formula returns 0. To use this formula, the cell ranges need to be the same size and of the same dimension (for example, if MyData is a range of 3 rows by 5 columns, YourData must also be 3 rows by 5 columns):
=SUM(IF( MyData =YourData,0,1))
The formula creates a new array of the same size as the ranges that you are comparing. The IF function fills the array with the value 0 and the value 1 (0 for mismatches and 1 for identical cells). The SUM function then returns the sum of the values in the array.
You can simplify the formula like this:
=SUM(1*( MyData <> YourData ))
Like the formula that counts error values in a range, this formula works because TRUE*1=1, and FALSE*1=0.
Find the location of the maximum value in a range
This array formula returns the row number of the maximum value in a single-column range named Data:
The IF function creates a new array that corresponds to the range named Data. If a corresponding cell contains the maximum value in the range, the array contains the row number. Otherwise, the array contains an empty string (""). The MIN function uses the new array as its second argument and returns the smallest value, which corresponds to the row number of the maximum value in Data. If the range named Data contains identical maximum values, the formula returns the row of the first value.
If you want to return the actual cell address of a maximum value, use this formula:
For more information, please visit the Excel help center.