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:
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:
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.