Excel 2010 Bible

Part II: Working with Formulas and Functions

380

#N/A). Figure 17.1 shows an example. The formula in cell C11 returns an error value because the

range that it sums (C4:C10) contains errors.

The following array formula, in cell C13, overcomes this problem and returns the sum of the val-

ues, even if the range contains error values:

{=SUM(IFERROR(C4:C10,””))}

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.

Note
The IFERROR function was introduced in Excel 2007. Following is a modified version of the formula that’s
compatible with older versions of Excel:

{=SUM(IF(ISERROR(C4:C10),””,C4:C10))}

New Feature
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.” n

FIGURE 17.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 num-

ber of error values in a range named Data:

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