Microsoft Office Tutorials and References

In Depth Information

**Summing Things Up**

Summing Things Up

Ah ha! Just when you think you know how to sum up numbers (really, haven’t

you been doing this since your early school years?) I present a fancy-footing

summing that makes you think twice before going for that quick total.

The functions here are very cool — very “in” with the math crowd. To be a

true Excel guru, try out the SUBTOTAL, SUMPRODUCT, SUMIF, and SUMIFS

functions shown here, and then strut your stuff around the office!

Using SUBTOTAL

The SUBTOTAL function is very flexible. It doesn’t perform just one

calculation, but rather it can do any one of 11 different calculations depending on

what you need. What’s more, SUBTOTAL can perform these calculations on

up to 255 ranges of numbers. This gives you the ability to get exactly the type

of summary you need without creating a complex set of formulas. The syntax

for the function follows:

=SUBTOTAL(function number, range1, range2,…)

The first argument determines which calculation is performed. It can be

any of the values shown in Table 8
-3. The remaining arguments identify the

ranges containing the numbers to be used in the calculation.

Table 8-3

Argument Values for the SUBTOTAL Function

Function Number

for First Argument

Function

Description

1

AVERAGE

Returns the average value of a group of

numbers

2

COUNT

Returns the count of cells that contain

numbers and also numbers within the list

of arguments

3

COUNTA

Returns the count of cells that are not empty

and only non-empty values within the list of

arguments

4

MAX

Returns the maximum value found in a

group of numbers

5

MIN

Returns the minimum value found in a group

of numbers

(continued)