How to Use the SUBTOTAL Function in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 6 min read · includes 2 free exercises

In short: SUBTOTAL applies a summary function to a range and can skip hidden rows. The formula is =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, ...])
CodeCalculation
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP

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.

AB
1ItemAmount
2Pen10
3Book20
4Cup30
5Bag40
=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

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.

Hint: the function code for SUM is 9.

Exercise 2: Count visible items

Write a formula that counts the non-empty cells in A2:A5 while skipping hidden cells.

Hint: COUNTA is 3, and adding 100 skips hidden cells.

What to learn next

Syntax and rules in this guide were checked against Google's SUBTOTAL help page. See all guides.