Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

The new AGGREGATE function, which works only in Excel 2010, provides another way

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

=AGGREGATE(9,2,C4:C10)

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 values 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 corresponding 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 specified, 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