Page 377 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 334
Part II
Working with Formulas and Functions
This formula works by creating a new array that contains the original values but without the errors. The IF
function effectively filters out error values by replacing them with an empty string. The SUM function then
works on this “filtered” array. This technique also works with other functions, such as AVERAGE, MIN, and
MAX.
If only Excel 2007 users will use your worksheet, you can use this more efficient version,
NEW FEATURE
NEW FEATURE
which uses the new IFERROR function:
{=SUM(IFERROR(G1:G7,””))}
FIGURE 18.1
An array formula can sum a range of values, even if the range contains errors.
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 con-
tain an error value). In this context, Excel treats FALSE as a 0 value. The array formula shown here per-
forms 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

334