Syntax
SUBTOTAL(function_num, ref1, ...)
| Parameter | Description |
|---|---|
| function_num | Parameter of the SUBTOTAL function. |
| ref1 | Parameter of the SUBTOTAL function. |
| ... | Parameter of the SUBTOTAL function. |
Examples
Sum visible rows
Formula
=SUBTOTAL(9, A1:A100)
Returns the SUM of only the visible values in A1:A100. Function number 9 = SUM. Hidden/filtered rows are excluded.
Count visible rows
Formula
=SUBTOTAL(102, B2:B500)
Returns the COUNT of visible cells. Function 102 = COUNT, ignoring both filtered and manually hidden rows (100-series ignores manual hiding too).
Average filtered data
Formula
=SUBTOTAL(1, C2:C50)
Returns the AVERAGE of visible values. Function 1 = AVERAGE. If you filter to show only Q1 data, SUBTOTAL averages only Q1.
Common Errors
#VALUE!
Invalid function number. The function_num must be 1-11 or 101-111. Other numbers return #VALUE!.
Tips
Key function numbers
1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 9=SUM, 11=VAR. Add 100 to also ignore manually hidden rows (101, 109, etc.).
Use with filtered tables
In a filtered table, =SUM shows the total of ALL rows. =SUBTOTAL(9,...) shows only the visible total. Always use SUBTOTAL with filters.
Avoids double-counting
SUBTOTAL ignores other SUBTOTAL cells in its range. This prevents double-counting when you have subtotals and a grand total.
Try SUBTOTAL in Viztab
Import your data and use SUBTOTAL with 370+ other formulas. No signup required.
Open Viztab