
Excel SUBTOTAL Function: Accurate Totals, Counts, and Averages for Filtered or Hidden Rows
When you apply a filter to a report, SUM can easily return an incorrect result because it adds everything. The solution is the Excel SUBTOTAL function. It automatically excludes filtered or hidden rows and handles totals, counts, averages, and maximum values in one formula.
Quick Fix
- Select the data range and turn on Data > Filter (Alt+A+T).
- In the cell for the total, enter
=SUBTOTAL(109, amount_range)→ returns a total excluding filtered and manually hidden rows. - For a table, use a structured reference such as
=SUBTOTAL(9, Table1[Amount]). - For a count, use
=SUBTOTAL(103, range)(COUNTA); for an average, use=SUBTOTAL(101, range)(AVERAGE).
SUBTOTAL at a Glance
Function Number Table (1–11 / 101–111)
| Function | Include Manually Hidden Rows | Exclude Manually Hidden Rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV.S | 7 | 107 |
| STDEV.P | 8 | 108 |
| SUM | 9 | 109 |
| VAR.S | 10 | 110 |
| VAR.P | 11 | 111 |
Rule: Rows hidden by filters are always excluded. Manually hidden rows are included with 1–11 and excluded with 101–111. Hidden columns are not excluded.
Nested formulas: Other SUBTOTAL formulas within a SUBTOTAL range are automatically ignored to prevent double-counting.
Why SUM Is Wrong and SUBTOTAL Is Right
SUM adds all values regardless of whether they are visible. SUBTOTAL calculates only visible rows—excluding filtered rows, and manually hidden rows when you use 101–111—so your report shows the true result.
Practical Example 1: Filtered Sales Report Total (9 vs. 109)
Sample Data
| Date | Region | Category | Qty | Price | Amount |
|---|---|---|---|---|---|
| 2025-10-01 | Seoul | A | 2 | 10000 | 20000 |
| 2025-10-02 | Busan | B | 1 | 15000 | 15000 |
| 2025-10-03 | Seoul | A | 3 | 10000 | 30000 |
| 2025-10-04 | Daegu | B | 5 | 8000 | 40000 |
| 2025-10-05 | Seoul | B | 4 | 12000 | 48000 |
Filter: Region=Seoul → visible Amount total = 98,000
Formulas
=SUBTOTAL(9, F2:F6)
=SUBTOTAL(109, F2:F6)
Practical Example 2: Tables and Structured References
- Create a table with Ctrl+T (named Sales).
- Total:
=SUBTOTAL(9, Sales[Amount]) - Also exclude manually hidden rows:
=SUBTOTAL(109, Sales[Amount])
Practical Example 3: Visible Item Counts, Averages, and Extremes
- Count numbers:
=SUBTOTAL(102, Sales[Qty]) - Count nonblank cells:
=SUBTOTAL(103, Sales[Product]) - Average:
=SUBTOTAL(101, Sales[Amount]) - Maximum/minimum:
=SUBTOTAL(104, Sales[Amount]),=SUBTOTAL(105, Sales[Amount])
Alternatives and Considerations
- AGGREGATE: Options such as
=AGGREGATE(9,7, Sales[Amount])can ignore hidden rows and errors. - Subtotal command: Use Data > Subtotal to automatically insert subtotals by group.
- PivotTables: Automatically create multidimensional summaries, subtotals, and grand totals.
- Cannot exclude hidden columns: SUBTOTAL is row-based. If you need to exclude hidden columns, redesign the calculation around vertical aggregation.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| The value does not change after filtering | Using SUM | Replace it with SUBTOTAL(9/109) |
| Manually hidden rows are included | Using codes 1–11 | Use codes 101–111 |
| Hidden columns are not excluded | The function is row-based | Use vertical aggregation or AGGREGATE |
| Double-counting | Using SUM for both higher- and lower-level totals | Use SUBTOTAL for the higher-level total too (nested SUBTOTAL formulas are automatically ignored) |
| #DIV/0! | Denominator is zero | Handle the exception with IF |
Wrap-Up and Related Articles
Calculating only the values you can see with SUBTOTAL makes your reports more reliable. Continue with the articles below to build on these skills.
- Complete Guide to Excel Tables and Structured References
- Advanced Filter and AutoFilter Patterns
- SUMIFS and Report Automation
- AGGREGATE Function: Options for Ignoring Hidden Rows and Errors
- How to Use PivotTable Subtotals and Grand Totals
Further reading (official documentation): SUBTOTAL function, AGGREGATE function, Subtotal command