SUBTOTAL Generator
Sum, count, or average a range while skipping rows removed by a filter.
SUBTOTAL
Sum, count, or average a range while skipping rows removed by a filter.
9 = SUM including manually hidden rows, 109 = SUM excluding them. See the tips for the full 1-11 / 101-111 list.
The range to aggregate. Keep it to the data rows only, not the header or the total row.
=SUBTOTAL(109, B2:B100)Worked Example
A filtered sales table with amounts in B2:B100, and you want a total that follows the filter.
=SUBTOTAL(109, B2:B100)Returns: Returns the sum of B2:B100 counting only visible rows. Filter the table down to the West region and the number immediately drops to the West-only total; rows you hid by hand are skipped too.
Checks Before You Paste
- •Both families skip rows hidden by an AutoFilter, so filtering behaves the same either way. The difference is manual hiding: 1-11 (9 = SUM) still includes rows you hid by hand, while 101-111 (109 = SUM) leaves them out.
- •SUBTOTAL only ignores hidden ROWS. Hiding a column changes nothing, so 109 across a horizontal range still adds the values in the columns you hid.
- •SUBTOTAL ignores any nested SUBTOTAL inside its own range, so a grand total over a column that already contains section subtotals will not double-count them. A plain SUM in the same place would.
How SUBTOTAL works
Availability: Excel 2003 and later, Excel for Mac, Excel for the web, Google Sheets and LibreOffice Calc. The 101-111 range needs Excel 2003 or newer; only the 1-11 codes existed before that.
SUBTOTAL runs one of eleven aggregations over a range and, unlike the plain function it replaces, leaves out the rows a filter has removed from view. That is what makes it the right total to put above an AutoFiltered table: filter to the West region and a SUBTOTAL sum immediately reports the West figure, where SUM keeps reporting the whole column. Excel's own Total Row on a formatted Table inserts SUBTOTAL for exactly this reason.
The first argument is a function number, and there are two families of them. 1 to 11 are AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR and VARP in that order, so 9 is SUM and 1 is AVERAGE. 101 to 111 are the same eleven in the same order, so 109 is SUM. Both families ignore rows hidden by a filter. The difference is manual hiding: 1-11 still include a row you hid by right-clicking and choosing Hide, while 101-111 leave it out.
SUBTOTAL only knows about hidden rows. Hiding a column changes nothing, so 109 across a horizontal range still adds the values in the columns you hid, and there is no function number that changes that. It also ignores any SUBTOTAL already inside its own range, which is what lets a grand total sit at the bottom of a column that already contains section subtotals without double-counting them - a plain SUM in the same place would count everything twice.
What SUBTOTAL will not do is skip error values. One #N/A from a lookup anywhere in the range makes the whole subtotal #N/A, filtered out or not, and that is where AGGREGATE takes over: it offers the same nineteen functions plus options to ignore errors, nested SUBTOTALs, hidden rows, or any combination. If you need a filtered total over a column that contains errors, AGGREGATE(9, 6, range) is the function, not SUBTOTAL.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)| Argument | Required | What it does |
|---|---|---|
function_num | Required | Which aggregation to run: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEVP, 9 SUM, 10 VAR, 11 VARP. Add 100 - so 101 to 111 - to also exclude manually hidden rows. |
ref1 | Required | The range to aggregate. Any SUBTOTAL formulas inside it are ignored, so nested section totals do not get counted again. |
ref2 | Optional | Up to 253 further ranges, aggregated together. They do not need to be adjacent, but 3D references across several sheets are not accepted. |
More worked examples
An AutoFiltered order table with amounts in D2:D2000, and a total in D1 that should track whatever the filter is currently showing.
=SUBTOTAL(9, D2:D2000)Returns: The sum of the visible rows. Filtering to one customer drops the figure to that customer's total with no formula change.
Putting the total above the table rather than below keeps it out of the filtered range, so it never hides itself.
The same filtered table, where you want to know how many rows the current filter is showing rather than their value.
=SUBTOTAL(103, A2:A2000)Returns: The count of visible non-blank cells in column A - the number of rows on screen.
103 is COUNTA, so it counts anything filled in. Use 102 (COUNT) if the column is numeric and blanks should not count.
A price list where some rows have been hidden by hand rather than filtered, because they are discontinued lines, and you want an average over the rest.
=SUBTOTAL(101, C2:C400)Returns: The mean of column C across the rows still visible, excluding the hidden ones.
SUBTOTAL(1, C2:C400) over the same range includes the hidden rows and gives a different number. This is the only situation where the two families disagree.
Common mistakes
- Assuming 9 and 109 always differ
- Under an AutoFilter they give identical answers - both families ignore filtered-out rows. The 100-series only changes the result when rows have been hidden manually. Choosing 109 as a habit is fine, but it is not what makes a total follow the filter.
- Hidden columns still counted
- SUBTOTAL looks at row visibility only. Hiding columns E to G and running 109 across B2:H2 still adds E, F and G. There is no function number that excludes hidden columns; delete them or reference only the columns you want.
- An error value in the range
- SUBTOTAL propagates errors. A single #DIV/0! makes the whole total an error even when the offending row is filtered out of view. AGGREGATE(9, 6, range) does the same sum while ignoring errors.
- The total row caught by its own filter
- A SUBTOTAL written directly beneath a filtered range often ends up inside the filter's range, so applying a filter hides the total itself. Leave a blank row, or put the total above the header.
- Hidden by grouping rather than filtering
- Rows collapsed with Group and Outline are hidden rows, so the 101-111 family excludes them and 1-11 includes them. That is easy to mistake for a filter effect when the outline is collapsed; check which mechanism is actually hiding the rows before changing the function number.
Frequently Asked Questions
Both skip rows removed by an AutoFilter. 9 still includes rows you hid manually by right-clicking and choosing Hide; 109 excludes them as well. If nothing on the sheet is ever hidden by hand, the two return the same number every time.
Yes, and it is deliberate. A grand total over a column containing section subtotals counts only the underlying rows, so nothing is added twice. It is the reason Excel's own Data - Subtotal command can stack summary rows inside a list safely, and the reason a plain SUM cannot be used there.
AGGREGATE when the range holds error values, or when you want LARGE, SMALL, MEDIAN or PERCENTILE, none of which SUBTOTAL offers. SUBTOTAL when neither applies - it is shorter, it is what a Table's Total Row inserts, and it works in versions older than 2010, which AGGREGATE does not.
Most often the rows are not actually filtered - they are hidden by hand, and the formula uses a 1-11 code. Otherwise the range may not overlap the filtered rows, or the sheet has calculation set to Manual. Switching 9 to 109 settles the first case immediately.
No. It takes multiple ranges but not a 3D reference, so =SUBTOTAL(9, Jan:Mar!B2:B100) is rejected. Write one SUBTOTAL per sheet and add the results, which also keeps each sheet's own filter state meaningful.