Microsoft Office Tutorials and References

In Depth Information

**Summing Formulas**

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 by using 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 255 arguments. The following formula, for example,

returns the sum of the values in ﬁ ve noncontiguous ranges:

17

=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 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 representations of numbers (which are interpreted as values), logical values, and even

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

A single cell reference:
B1

■

A literal value:
5

■

A string that looks like a value:
"6"

■

A missing argument:
, ,

■

An expression that uses another function:
SQRT(4)

■

A range reference:
A1:A5

■

A logical value:
TRUE

■