SUBTOTAL
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Syntax
SUBTOTAL(function_num,ref1,ref2, …)
Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.
Function_Num |
Function |
1 |
AVERAGE |
2 |
COUNT |
3 |
COUNTA |
4 |
MAX |
5 |
MIN |
6 |
PRODUCT |
7 |
STDEV |
8 |
STDEVP |
9 |
SUM |
10 |
VAR |
11 |
VARP |
Ref1,ref2, … are 1 to 29 ranges or references for which you want the subtotal.
Remarks
- If there are other subtotals within ref1,ref2, … (or nested subtotals), these nested subtotals are ignored to avoid double counting.
- SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.
- If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.
Example
SUBTOTAL(9,C3:C5)
will generate a subtotal of the cells C3:C5 using the SUM function