Yes, you can use the SUBTOTAL command in Excel to sum filtered data. But if you’re typing =SUBTOTAL(9,A2:A1000) without understanding function numbers or hiding rows manually, you’ll get wrong totals every time.
The Problem
You’ve got sales data in A1:E27—names, regions, dates, units sold, and revenue. Someone applied AutoFilter on Region, then hid rows manually (Ctrl+9) to ‘clean up’ the view. You slap =SUM(E2:E27) in E28. It returns $312,480. But only 12 rows are visible—and three of them are hidden by filter, not row hiding. Your total includes $67,210 from inactive regions. Worse: your colleague copies that SUM down to a report tab, and no one notices until finance flags a $42K variance.
| Name | Region | Date | Units | Revenue |
|---|---|---|---|---|
| Sarah Chen | APAC | 2024-03-15 | 14 | $18,200 |
| James Okafor | EMEA | 2024-03-16 | 9 | $12,600 |
| Maya Patel | NA | 2024-03-17 | 22 | $29,700 |
| Diego Ruiz | LATAM | 2024-03-18 | 7 | $8,400 |
| Aiko Tanaka | APAC | 2024-03-19 | 19 | $25,650 |
| Liam Byrne | EMEA | 2024-03-20 | 13 | $17,550 |
| Nina Dubois | NA | 2024-03-21 | 16 | $21,600 |
| Tariq Hassan | MENA | 2024-03-22 | 11 | $14,850 |
| Elena Petrova | EMEA | 2024-03-23 | 8 | $10,800 |
| Rajiv Mehta | APAC | 2024-03-24 | 25 | $33,750 |
This table shows raw data—no filters, no hidden rows. Yet most users apply =SUM(E2:E11) here and call it done. That’s fine… until they filter for APAC only and forget to update the formula.
The Solution
- Select your data range (A1:E11). Press Alt + A + T — this opens the Subtotal dialog.
- In 'At each change in', pick Region (column B).
- In 'Use function', choose Sum.
- In 'Add subtotal to', check Revenue (column E).
- Uncheck 'Replace current subtotals' if you’re testing. Click OK.
Excel inserts outline rows with collapsible groups and automatic =SUBTOTAL(9,E2:E11) formulas. These ignore hidden rows—even ones hidden by filters. Try filtering Region = APAC now. The subtotal at the bottom updates to $77,600. Not $312,480. Not $124,350. Exactly what’s visible.
| Region | Revenue | Subtotal |
|---|---|---|
| APAC | $18,200 | |
| APAC | $25,650 | $77,600 |
| APAC | $33,750 | |
| EMEA | $12,600 | $40,950 |
| EMEA | $17,550 | |
| EMEA | $10,800 | $40,950 |
| NA | $29,700 | |
| NA | $21,600 | $51,300 |
| Total | $170,850 |
That final $170,850? It’s =SUBTOTAL(9,E2:E11). It updates instantly when you collapse APAC or hide rows with Ctrl+9. No manual edits. No errors.
Going Further
You don’t need the dialog box. Type =SUBTOTAL(9,E2:E11) directly into any cell. Function number 9 = SUM. Use 1 for AVERAGE, 2 for COUNT, 4 for MAX, 5 for MIN, 109 for SUM ignoring hidden rows *and* manually hidden rows (not just filtered ones). Yes—109 is different from 9. Most people miss that.
Try this: Hide row 5 manually (right-click → Hide), then apply filter for EMEA. With =SUBTOTAL(9,E2:E11), row 5 stays excluded. With =SUBTOTAL(109,E2:E11), it’s still excluded—but if you unhide row 5 *and* clear the filter, both return identical results. 109 is safer for reports where users might hide rows outside filters.
You can nest SUBTOTAL inside IF: =IF(SUBTOTAL(103,A2:A11)>0,SUBTOTAL(9,E2:E11),"No data"). 103 counts visible cells — useful for dynamic headers.
When NOT to Use This
Don’t use SUBTOTAL on non-contiguous ranges. =SUBTOTAL(9,A2:A5,C2:C5) returns #VALUE!. Excel doesn’t support it.
Avoid it in tables with merged cells. If column B has merged Region headers spanning 3 rows, SUBTOTAL fails silently—or worse, returns zero. Unmerge first.
Never use it in shared workbooks with Track Changes enabled. SUBTOTAL formulas break when multiple users edit simultaneously. Switch to AGGREGATE instead—it’s more stable and supports error ignoring.
If your data has blanks in the grouping column (e.g., empty Region cells), SUBTOTAL treats them as a separate group. That creates phantom subtotals. Clean missing values first with =IF(B2="","Unknown",B2).
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Subtotal dialog | Alt + A + T | Works only when data is selected |
| Hide selected rows | Ctrl + 9 | SUBTOTAL(109) respects this; 9 does not |
| Toggle outline levels | Alt + Shift + 0–9 | 0 = show all, 1 = top level only |
| Recalculate all formulas | F9 | Critical after filtering—SUBTOTAL updates live, but F9 forces refresh |
