SUBTOTAL runs one of eleven aggregate calculations (SUM, AVERAGE, COUNT, MAX, and so on) over a range, with one important twist: it can ignore rows hidden by a filter. That is why the total at the bottom of a filtered list should almost always be a SUBTOTAL, not a SUM. It also skips other SUBTOTAL results in the range, so nested totals never double-count.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
- function_num - a code (see the table) that picks the calculation and whether to ignore hidden rows.
- ref1, ref2, ... - the ranges to aggregate.
Function Numbers
Each calculation has two codes. The 1-11 version ignores only filtered-out rows. The 101-111 version also ignores rows you hide manually.
| Calculation | Includes manually hidden | Ignores manually hidden |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
Sum a Filtered List
With numbers in C2:C100:
=SUBTOTAL(9, C2:C100)
Apply a filter to the list and the total instantly reflects only the visible rows. A plain =SUM(C2:C100) would keep counting the filtered-out rows.
Count Visible Rows
=SUBTOTAL(3, C2:C100)
Function 3 (COUNTA) counts the non-empty visible cells, giving a live count of filtered results.
Ignore Manually Hidden Rows Too
If you hide rows by right-clicking and choosing Hide (not with a filter), switch to the 100-series code to exclude them as well:
=SUBTOTAL(109, C2:C100)
Common Problems
- The total does not change when I filter. You used SUM, not SUBTOTAL. Only SUBTOTAL (and AGGREGATE) respond to filtering.
- Manually hidden rows are still counted. You used a 1-11 code. Use the 101-111 version (for example 109 instead of 9) to drop hand-hidden rows.
- Nested subtotals look wrong. They are not, actually: SUBTOTAL deliberately ignores other SUBTOTAL cells inside its range, so a grand total of subtotals does not double up.
SUBTOTAL vs AGGREGATE
AGGREGATE is a newer function that does everything SUBTOTAL does and can also ignore error values, which SUBTOTAL cannot. If your range may contain errors, =AGGREGATE(9, 7, C2:C100) sums the visible rows while skipping errors. For plain filtered totals, SUBTOTAL is simpler and works in every version.
Frequently Asked Questions
What does the SUBTOTAL function do?
SUBTOTAL performs a chosen calculation (sum, average, count, and others) over a range while ignoring rows hidden by a filter. It is the correct function for a total that should reflect only the currently visible, filtered rows.
What is the difference between SUBTOTAL 9 and 109?
Both ignore rows removed by a filter. Code 9 still includes rows you hid manually; code 109 ignores manually hidden rows as well. Use 109 when you hide rows by hand and want them left out of the total.
Why use SUBTOTAL instead of SUM?
SUM always adds every cell in the range, even ones hidden by a filter, so a filtered view shows a total that includes rows you cannot see. SUBTOTAL with function 9 recalculates to include only the visible rows.
Does SUBTOTAL ignore other subtotals in the range?
Yes. SUBTOTAL skips any other SUBTOTAL results inside its range, so you can place group subtotals through a list and a grand SUBTOTAL at the bottom without double-counting.