Part II: Working with Formulas and Functions
300
You can use complete rows or columns as an argument for the SUM function. The formula that fol-
lows, for example, returns the sum of all values in column A. If this formula appears in a cell in
column A, it generates a circular reference error.=SUM(A:A)The following formula returns the sum of all values on Sheet1 by using a range reference that con-
sists of all rows. To avoid a circular reference error, this formula must appear on a sheet other than
Sheet1.=SUM(Sheet1!1:1048576)The SUM function is very versatile. The arguments can be numerical values, cells, ranges, text rep-
resentations of numbers (which are interpreted as values), logical values, and even embedded func-
tions. For example, consider the following formula:=SUM(B1,5,”6”,,SQRT(4),A1:A5,TRUE)This odd formula, which is perfectly valid, contains all the following types of arguments, listed
here in the order of their presentation:l (^) A single cell reference: B1
l A literal value: 5
l (^) A string that looks like a value: “6”
l A missing argument: , ,
l (^) An expression that uses another function: SQRT(4)
l A range reference: A1:A5
l (^) A logical value: TRUE
Caution
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. However, 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 13.11 illustrates a cumulative sum. Column B shows the monthly amounts, and col-
umn C displays the cumulative (year-to-date) totals.The formula in cell C2 is=SUM(B$2:B2)Notice that this formula uses a mixed reference — that is, 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