Summing Formulas

The examples in this section demonstrate how to perform common summing tasks by using formulas. The formulas range from very simple to relatively complex array formulas that compute sums of cells that match multiple criteria.

Summing All Cells in a Range

It doesn't get much simpler than this. The following formula returns the sum of all values in a range named Data:

 =SUM(Data) 

The SUM function can take up to 30 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 generates a circular reference error.

 =SUM(A:A) 

The following formula returns the sum of all values on Sheet1. 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 representations of numbers (which are interpreted as values), logical values, array constants, and even embedded functions. For example, consider the following formula:

 =SUM(B1,5,"6",,SQRT(4),{1,2,3},A1:A5,TRUE) 

This formula, which is a perfectly valid formula, contains all the following types of arguments, listed here in the order of their presentation:

• A single cell reference

• A literal value

• A string that looks like a value

• A missing argument

• An expression that uses another function

• An array constant

• A range reference

• A logical TRUE value

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. 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-12 illustrates a cumulative sum. Column B shows the monthly amounts, and column C displays the cumulative (year-to-date) totals.

Figure 7-12: 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)) 

Figure 7-13 shows this formula at work.

Figure 7-13: Using an IF function to hide cumulative sums for missing data.

On the CD 

The workbook  cumulative sum.xlsx is available on the companion CD-ROM.

Summing the "Top n" Values

In some situations, you may need to sum the n largest values in a range-for example, the top ten values. One approach is to sort the range in descending order and then use the SUM function with an argument consisting of the first n values in the sorted range. An array formula such as this one accomplishes the task without sorting:

 {=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))} 

This formula sums the ten largest values in a range named Data. To sum the ten smallest values, use the SMALL function instead of the LARGE function:

 {=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))} 

These formulas use an array constant comprising the arguments for the LARGE or SMALL function. If the value of n for your top-n calculation is large, you may prefer to use the following variation. This formula returns the sum of the top 30 values in the Data range. You can, of course, substitute a different value for 30.

 {=SUM(LARGE(Data,ROW(INDIRECT("1:30"))))} 

Cross Ref 

See Chapter 14 for more information about array constants.