SUBTOTAL function
Summary
Returns a subtotal in a list or database
Syntax
SUBTOTAL(function_num,ref1,[ref2],...)
The SUBTOTAL function syntax has the following arguments:
• Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
1 | 101 | AVERAGE
2 | 102 | COUNT
3 | 103 | COUNTA
4 | 104 | MAX
5 | 105 | MIN
6 | 106 | PRODUCT
7 | 107 | STDEV
8 | 108 | STDEVP
9 | 109 | SUM
10 | 110 | VAR
11 | 111 | VARP
• Ref1 Required. The first named range or reference for which you want the subtotal.
• Ref2,... Optional. Named ranges or references 2 to 254 for which you want the subtotal.
• Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
1 | 101 | AVERAGE
2 | 102 | COUNT
3 | 103 | COUNTA
4 | 104 | MAX
5 | 105 | MIN
6 | 106 | PRODUCT
7 | 107 | STDEV
8 | 108 | STDEVP
9 | 109 | SUM
10 | 110 | VAR
11 | 111 | VARP
• Ref1 Required. The first named range or reference for which you want the subtotal.
• Ref2,... Optional. Named ranges or references 2 to 254 for which you want the subtotal.
Example
=SUBTOTAL(9,A2:A5)
=SUBTOTAL(1,A2:A5)
=SUBTOTAL(1,A2:A5)