How to Use SUBTOTAL in Excel
Sums, counts or averages only the visible rows — the only aggregate that behaves correctly when you filter a table.
What is SUBTOTAL in Microsoft Excel?
SUBTOTAL runs a chosen aggregate and optionally ignores rows hidden by an AutoFilter. That is the whole point: SUM keeps adding filtered-out rows, but SUBTOTAL(9, range) recalculates to show only what is on screen. Function numbers 1 to 11 include manually hidden rows; 101 to 111 ignore them too.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| function_num | Required | A code for the aggregate: 9 = SUM, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN. Add 100 to also ignore manually hidden rows. |
| ref1 | Required | The first range to aggregate. |
| ref2 | Optional | Additional ranges, up to 254 in total. |
When to Use SUBTOTAL (Real-World Business Scenarios)
- 1
Putting a live total under a filtered table so the number matches the rows the user can see.
- 2
Building dashboards where managers filter by region and expect the total to follow.
- 3
Avoiding double counting when subtotal rows already exist inside a range, because SUBTOTAL ignores other SUBTOTAL results.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Region | Amount |
|---|---|
| North | 55000 |
| South | 48000 |
| North | 1200 |
How it works: With no filter it totals everything. Filter the Region column to North and the same formula instantly drops to 56,200, because the hidden South row is excluded.
Common Errors & How to Fix Them
Why it happens: function_num was 1 to 11 instead of 101 to 111, so filtered rows were still counted.
Fix: Switch to the 100-series code: =SUBTOTAL(109,B2:B4) for a sum that respects filters and hidden rows.
Why it happens: The range already contains SUBTOTAL rows, which are skipped by design to avoid double counting.
Fix: Point the formula at the raw detail rows only, or use SUM if nested subtotals must be included.
Try SUBTOTAL Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SUBTOTAL)
What does the SUBTOTAL formula do in Excel?
SUBTOTAL runs a chosen aggregate and optionally ignores rows hidden by an AutoFilter. That is the whole point: SUM keeps adding filtered-out rows, but SUBTOTAL(9, range) recalculates to show only what is on screen. Function numbers 1 to 11 include manually hidden rows; 101 to 111 ignore them too.
What is the syntax for SUBTOTAL?
What are common mistakes when using SUBTOTAL?
Total does not change when filtering: function_num was 1 to 11 instead of 101 to 111, so filtered rows were still counted. (Switch to the 100-series code: =SUBTOTAL(109,B2:B4) for a sum that respects filters and hidden rows.) Result is lower than expected: The range already contains SUBTOTAL rows, which are skipped by design to avoid double counting. (Point the formula at the raw detail rows only, or use SUM if nested subtotals must be included.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
Master SUBTOTAL in Intermediate Excel
Learn SUBTOTAL along with our formula library, practice files, and verifiable certificates in our complete curriculum.