Excel 2000 in a Nutshell

Name

SUBTOTAL

Synopsis

You can use SUBTOTAL to total a series of values using one of 11 different functions. When you use this function you specify the values that you want to total and then select the function you want to use.

This function works well with the Data → Subtotals option. In fact, it is easier to create the data list using the menu option. When you use the Subtotals option it simply creates a formula using SUBTOTAL.

To Calculate

=SUBTOTAL(Function_Num, Ref1, Ref2,...)

The Function_Num argument is required for this function. You must specify at least one Ref argument.

Function_Num

An integer value between 1 and 11 that specifies the function that you want to use to sum the list of values, as specified in Table 14-3.

Table 14-3. Options for Totaling Values

Value	Function Called
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

Ref1, Ref2,...

There must be between 1 and 29 numeric values specified as arguments for this function.

Example

Figure 14-23 shows how SUBTOTAL is used to perform several different types of calculations on a series of numeric values. The only thing that is different for each use of the function is the value specified for the Function_Num argument.

Figure 14-23. SUBTOTAL provides the ability to quickly modify the function used with a series of numbers