Home › Excel functions › SUBTOTAL
SUBTOTAL Function in Excel: Sum Filtered and Visible Cells
SUBTOTAL calculates a sum, count, average or other statistic for a range, but ignores rows that are hidden by a filter. Filter your list on one region and the total updates to show just that region.
SUBTOTAL · In this guide
SUBTOTAL syntax
=SUBTOTAL(function_num, ref1, [ref2], …)| Argument | What it means |
|---|---|
| function_num | A number that tells SUBTOTAL which calculation to do, such as 9 for SUM or 109 for SUM ignoring hidden rows. See the table below. |
| ref1, ref2, … | The range or ranges to calculate on. |
Function numbers
| Calculation | Includes manually hidden rows | Ignores manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT (numbers) | 2 | 102 |
| COUNTA (non-empty) | 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 |
1–11 vs. 101–111: both versions ignore rows hidden by a filter. The difference is rows you hide yourself (right-click › Hide): 1–11 still include them, 101–111 leave them out. If you're unsure, use 101–111: they always calculate only what you can see.
Example: sum a filtered list
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Anna | 1,200 |
| 3 | South | Ben | 800 |
| 4 | North | Chris | 950 |
| 5 | East | Dana | 450 |
| 6 | South | Anna | 700 |
| 7 | Total | 4,100 |
With no filter, C7 shows 4,100, the same as SUM. Now filter column A on "North" (Data › Filter). Rows 3, 5 and 6 are hidden and the total changes to 2,150: only the visible rows are added. A normal =SUM(C2:C6) would keep showing 4,100.
Count or average visible rows
- Count the visible rows:
=SUBTOTAL(103, A2:A6)counts the non-empty visible cells. After filtering on North, it returns 2. - Average of what's visible:
=SUBTOTAL(101, C2:C6)returns 1,075 for North. - Highest visible value:
=SUBTOTAL(104, C2:C6)returns 1,200 for North.
Tip: put the SUBTOTAL formulas above the list instead of below it. Then they stay visible when the list gets long.
SUBTOTAL ignores other subtotals
If a range contains cells that themselves use SUBTOTAL, those cells are skipped. That means you can add subtotals per group and a grand total over the whole column, without counting anything twice. This is exactly what Excel's built-in Data › Subtotal command does: it inserts SUBTOTAL formulas for each group and one for the grand total. The total row of an Excel table (Ctrl+T, then Table Design › Total Row) uses SUBTOTAL too.
Common mistakes
- The total doesn't change when you hide rows manually. You used 1–11. Switch to 101–111.
- Hidden columns are still included. SUBTOTAL only ignores hidden rows. It doesn't skip hidden columns in a horizontal range.
- #VALUE! The function number isn't one of 1–11 or 101–111.
- You need to ignore errors as well. Use AGGREGATE, which works like SUBTOTAL but can also skip error values:
=AGGREGATE(9, 7, C2:C6).
Frequently asked questions
What is the difference between SUBTOTAL 9 and 109?
Both add up only the rows that are visible after filtering. 109 also skips rows you've hidden manually; 9 still includes them.
How do I sum only visible cells in Excel?
Use =SUBTOTAL(109, range). It ignores rows hidden by a filter and rows hidden manually.
Why use SUBTOTAL instead of SUM?
SUM always adds every cell in the range. SUBTOTAL respects filters and skips other subtotals, so it gives the right total for whatever is currently visible.
What is AGGREGATE?
AGGREGATE is a more powerful version of SUBTOTAL, available since Excel 2010. It offers more calculations and can also ignore error values.