SUBTOTAL: totals that respond to filters
SUBTOTAL is helpful in a filtered list because it can calculate on the rows that remain in view. Hidden rows and nested subtotals need extra care.
The function number chooses both the aggregation and the hidden-row behavior. It is not a row count. For a sum, 9 and 109 share the same calculation but differ when a user manually hides a row. Filtering excludes rows for either choice.
| Formula | Expected result / interpretation |
|---|---|
| =SUBTOTAL(109,B2:B4) | Sum while excluding filtered-out and manually hidden rows |
| =SUBTOTAL(9,B2:B4) | Sum while excluding filtered-out rows, retaining manually hidden rows |
| View | SUM(B2:B4) | SUBTOTAL(9,B2:B4) | SUBTOTAL(109,B2:B4) |
|---|---|---|---|
| All visible | 600 | 600 | 600 |
| Row 3 manually hidden | 600 | 600 | 400 |
| Row 3 filtered out | 600 | 400 | 400 |
Enter amounts 100, 200 and 300 in B2:B4. Start with all rows visible, then manually hide row 3. Finally unhide it and apply a filter that excludes that row.
Compare full-list and visible-row totals
Formula syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
What goes into it
function_num
Code identifying SUM, AVERAGE, COUNT and whether manually hidden rows are ignored.
ref1
First reference to subtotal.
ref2
Optional additional references.
A quick example first
=SUBTOTAL(9,B2:B100)
Sums visible filtered rows; function code 9 is SUM.
=SUBTOTAL(109,B2:B100)
SUM variant that also ignores manually hidden rows.
Before you copy this into a real sheet
This example is a vertical list in Excel. Do not assume hiding columns produces the same behavior. Nested SUBTOTAL results inside the referenced range are ignored to reduce double counting, but ordinary SUM totals are not automatically identified as subtotals. Keep detail rows and final totals clearly separated. A visible-row figure changes as someone changes the filter, so label dashboards accordingly and preserve the selected filter when sharing a report.
Useful habits
- Use 1–11 versus 101–111 intentionally depending on hidden-row behavior.
- SUBTOTAL automatically ignores other SUBTOTAL results inside its reference to reduce double counting.
- It is useful for filtered lists and table totals.
Where people get caught out
- Using the wrong function code and getting different hidden-row behavior than intended.
- Assuming SUBTOTAL behaves exactly like SUM when nested subtotals exist.
Other functions worth a look
A detail worth remembering
Compare a filtered result with a small hand-counted sample. The function number controls how hidden rows are handled.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.