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 fi rst argument ( 9 ) specifi 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))}
Search JabSto ::




Custom Search