Microsoft Office Tutorials and References

In Depth Information

The AGGREGATE function, which works only in Excel 2010 and later, provides another

way to sum a range that contains one or more error values. Here's an example:

=AGGREGATE(9,2,D4:D10)

The first argument, 9, is the code for SUM. The second argument, 2, is the code for “ignore error values.”

Counting the number of error values in a range

The following array formula is similar to the previous example, but it returns a count of the number of error val-

ues in a range named
Data
:

{=SUM(IF(ISERROR(Data),1,0))}

This formula creates an array that consists of 1s (if the corresponding cell contains an error) and 0s (if the cor-

responding cell does not contain an error value).

You can simplify the formula a bit by removing the third argument for the IF function. If this argument isn't spe-

cified, the IF function returns FALSE if the condition is not satisfied (that is, the cell does not contain an error

value). In this context, Excel treats FALSE as a 0 value. The array formula shown here performs exactly like the

previous formula, but it doesn't use the third argument for the IF function:

{=SUM(IF(ISERROR(Data),1))}

Actually, you can simplify the formula even more:

{=SUM(ISERROR(Data)*1)}

This version of the formula relies on the fact that:

TRUE * 1 = 1

and

FALSE * 1 = 0

Summing the n largest values in a range

The following array formula returns the sum of the ten largest values in a range named
Data
:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:10”))))}

The LARGE function is evaluated ten times, each time with a different second argument (1, 2, 3, and so on up

to 10). The results of these calculations are stored in a new array, and that array is used as the argument for the

SUM function.