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)
Search JabSto ::




Custom Search