Page 309 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 266
Part II
Working with Formulas and Functions
The SUM function can take up to 255 arguments. The following formula, for example, returns the sum of
the values in five noncontiguous ranges:
=SUM(A1:A9,C1:C9,E1:E9,G1:G9,I1:I9)
You can use complete rows or columns as an argument for the SUM function. The formula that follows, for
example, returns the sum of all values in column A. If this formula appears in a cell in column A, it gener-
ates a circular reference error.
=SUM(A:A)
The following formula returns the sum of all values on Sheet1 by using a range reference that consists 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 representa-
tions of numbers (which are interpreted as values), logical values, and even embedded functions. For exam-
ple, consider the following formula:
=SUM(B1,5,”6”,,SQRT(4),A1:A5,TRUE)
This odd formula, which is perfectly valid, contains all of the following types of arguments, listed here in
the order of their presentation:
n A single cell reference
n A literal value
n A string that looks like a value
n A missing argument
n An expression that uses another function
n A range reference
n A logical TRUE value
CAUTION The SUM function is versatile, but it’s also inconsistent when you use logical values (TRUE or
CAUTION
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 14.11 illustrates a cumulative sum. Column B shows the monthly amounts, and column 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 range argument
adjusts such that the sum always starts with row 2 and ends with the current row. For example, after copy-
ing this formula down column C, the formula in cell C8 is
=SUM(B$2:B8)

266