Microsoft Office Tutorials and References
In Depth Information
Working with Tables
About the SUBTOTAL function
The SUBTOTAL function is very versatile, but it’s also one of the most confusing functions in
Excel’s arsenal. First of all, it has a misleading name because it does a lot more than addition.
The first argument for this function requires an arbitrary (and impossible to remember) number
that determines the type of result that’s returned. Fortunately, the Excel Formula AutoComplete
feature helps you insert these numbers.
In addition, the SUBTOTAL function was enhanced in Excel 2003 with an increase in the number
of choices for its first argument, which opens the door to compatibility problems if you share
your workbook with someone who uses an earlier version of Excel.
The first argument for the SUBTOTAL function determines the actual function used. For
example, when the first argument is 1, the SUBTOTAL function works like the AVERAGE function. The
following table shows the possible values for the first argument for the SUBTOTAL function:
Value
Function
1
AVERAGE
2
COUNT
3
COUNTA
4
MAX
5
MIN
6
PRODUCT
7
STDEV
8
STDEVP
9
SUM
10
VAR
11
VARP
101*
AVERAGE
102*
COUNT
103*
COUNTA
104*
MAX
105*
MIN
106*
PRODUCT
107*
STDEV
108*
STDEVP
109*
SUM
110*
VAR
111*
VARP
*Excel 2003 and later
 
Search JabSto ::




Custom Search