SUBTOTAL calculates a sum, count, average, or another aggregate that responds to filtering. For a filtered sales range in B2:B6, use =SUBTOTAL(9,B2:B6).
Rows hidden by a filter are excluded with either code 9 or 109. Code 109 also excludes rows you hide manually.
Sum Visible Filtered Rows With SUBTOTAL
Enter this sample data in A1:B6:
| Region | Sales |
|---|---|
| East | 120 |
| West | 80 |
| East | 150 |
| West | 50 |
| East | 100 |
=SUBTOTAL(9,B2:B6)Before filtering, the formula returned 500. After the Region filter showed only East, it returned 370.
A plain SUM would still include the filtered-out West rows. SUBTOTAL is designed to recalculate from the visible filtered rows.
SUBTOTAL Function Syntax and Codes
=SUBTOTAL(function_code,range1,[range2,...])function_code chooses the calculation. range1 is the first range, and later ranges are optional.
| Calculation | Includes manually hidden rows | Excludes manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Both code columns exclude rows hidden by a filter. The difference applies to rows hidden manually.
Understand SUBTOTAL 9 Versus 109
The live test compared these formulas over the same B2:B6 range:
=SUBTOTAL(9,B2:B6)
=SUBTOTAL(109,B2:B6)With West filtered out, both formulas returned 370.
After the filter was removed and row 4 was hidden manually, code 9 returned 500. Code 109 returned 350 because it excluded that row’s value of 150.
Count or Average Visible Rows
Change the code without changing the range. Code 3 counts non-empty visible cells:
=SUBTOTAL(3,A2:A6)Use code 2 instead when only numeric cells should count. For an average, use code 1 or 101.
The same function can therefore power a compact filtered summary with visible-row count, average, minimum, maximum, and total.
Avoid Double Counting Nested SUBTOTAL Formulas
SUBTOTAL ignores cells in its ranges that already contain SUBTOTAL formulas.
In the test, B8 and B9 contained 25 and 35. B10 contained =SUBTOTAL(9,B8:B9), which returned 60.
=SUBTOTAL(9,B8:B10)The outer formula also returned 60. It did not add the inner subtotal and double the result to 120.
Use SUBTOTAL Across Multiple Ranges
Add more range arguments when the same aggregation should cover separate blocks:
For example, if D2:D6 contains 10, 20, 30, 40, and 50, combine that block with the sales values in B2:B6:
=SUBTOTAL(9,B2:B6,D2:D6)SUBTOTAL combines both ranges under the selected code. The ranges do not need to be adjacent.
SUBTOTAL has no criteria argument. Use SUMIF or SUMIFS when rows should qualify by a condition rather than current visibility.
See Google’s SUBTOTAL reference for the full code mapping and filter behavior.
Other Google Sheets articles you may also like