How to Use the SUBTOTAL Function in Google Sheets
=SUBTOTAL(function_code, range1, [range2, ...]). Code 9 sums, 1 averages, 3 counts non-empty cells.
What SUBTOTAL does
A plain SUM adds every cell, including rows you filtered out. SUBTOTAL can follow the filter, so a total at the top of a table changes as you filter. It also ignores any cell in the range that already contains a SUBTOTAL, which prevents double counting.
SUBTOTAL syntax
=SUBTOTAL(function_code, range1, [range2, ...])
| Code | Calculation |
|---|---|
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN |
6 | PRODUCT |
7 | STDEV |
8 | STDEVP |
9 | SUM |
10 | VAR |
11 | VARP |
Per Google's help page, adding 10 to a single-digit code (or 1 to a two-digit code) also skips hidden cells. For example, 102 counts only visible cells and 110 calculates variance over visible cells only. Rows hidden by a filter are always excluded, whichever code you use.
A step-by-step example
Here are four items and amounts in cells A1:B5.
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Pen | 10 |
| 3 | Book | 20 |
| 4 | Cup | 30 |
| 5 | Bag | 40 |
=SUBTOTAL(9, B2:B5)
This returns 100. If you filter the table to hide Cup and Bag, the same formula returns 30, because filtered rows are always excluded. To count the visible items:
=SUBTOTAL(103, A2:A5)
This returns 4 while all rows are visible. Code 103 is COUNTA with hidden cells skipped.
Common mistakes
- Using SUM on filtered data. SUM still adds the hidden rows.
- Mixing up the codes. 9 is SUM, 3 is COUNTA, and 1 is AVERAGE.
- Expecting manually hidden rows to be skipped by code 9. Only the codes with the extra 100 or 10 are described as skipping hidden cells.
Try it yourself
Use the table above (A1:B5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Total with code 9
Write a formula that sums the amounts in B2:B5 with SUBTOTAL.
Exercise 2: Count visible items
Write a formula that counts the non-empty cells in A2:A5 while skipping hidden cells.
What to learn next
- AVERAGEIFS: average with several conditions.
- SUMIF: add up the rows that match a condition.
- SUMIFS: add up values that meet several conditions.
Syntax and rules in this guide were checked against Google's SUBTOTAL help page. See all guides.