Microsoft Office Tutorials and References

In Depth Information

**Ignoring errors when summing**

=IF(B2<>"",SUM(B$2:B2),"")

Figure 17.12 shows this formula at work.

FIGURE 17.12

Using an
IF
function to hide cumulative sums for missing data

17

Ignoring errors when summing

The
SUM
function does not work if the range to be summed includes any errors. For

example, if one of the cells to be summed displays
#N/A
, the
SUM
function will also

return
#N/A
.

To add the values in a range and ignore the error cells, use the
AGGREGATE
function. For

example, to sum a range named Data (which may have error values), use this formula:

=AGGREGATE(9,6,Data)

The
AGGREGATE
function is very versatile, and can do a lot more than just add values. In

this example, the ﬁ rst argument (
9
) speciﬁ es
SUM
. The second argument (
6
), means ignore

error values.

The arguments are described in the Excel Help. Excel also provides good autocomplete

assistance when you enter a formula that uses this function.

The
AGGREGATE
function was introduced in Excel 2010. For compatibility with earlier versions use this array

formula:

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