Microsoft Office Tutorials and References

In Depth Information

**Summing Formulas**

h
A range reference

h
A logical TRUE value

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.

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 cumulative (year-to-date) totals.

Figure 7-13:
Simple formulas in column C display a cumulative sum of the values in column B.

The formula in cell C2 is

=SUM(B$2:B2)

Notice that this formula uses a
mixed
reference.
The first cell in the range reference always refers

to the same row (in this case, row 2). When this formula is copied down the column, the range

argument adjusts such that the sum always starts with row 2 and ends with the current row. For

example, after copying this formula down column C, the formula in cell C8 is

=SUM(B$2:B8)

You can use an IF function to hide the cumulative sums for rows in which data hasn’t been

entered. The following formula, entered in cell C2 and copied down the column, is

=IF(ISBLANK(B2),””,SUM(B$2:B2))