Microsoft Office Tutorials and References

In Depth Information

The examples in this section demonstrate how to perform common summing tasks by using formulas. The for-

mulas range from very simple to relatively complex array formulas that compute sums of cells that match mul-

tiple 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 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 ex-

ample, 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