Microsoft Office Tutorials and References

In Depth Information

The SUM function is versatile, but it's also inconsistent when you use logical values

(TRUE or FALSE). Logical values stored in cells are always treated as 0. But logical

TRUE, when used as an argument in the SUM function, is treated as 1.

Summing a range that contains errors

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

first argument (9) specifies SUM. The second argument (6) means to 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))}

Computing a cumulative sum

You may want to display a cumulative sum of values in a range — sometimes known as a
running total.
Figure

7-13 illustrates a cumulative sum. Column B shows the monthly amounts, and column C displays the cumulat-

ive (year-to-date) totals.