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