Part of the free Module 4: Sort and Filter · Lesson 10 of 11 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
SUBTOTAL and AGGREGATE are the two Excel functions that calculate only the visible rows of a filtered list. =SUBTOTAL(109,E2:E100) sums the rows that pass the filter and ignores hidden ones; =AGGREGATE(9,5,E2:E100) does the same and can also skip errors. SUM, COUNT and AVERAGE include every row whether it is visible or not.
Why SUM is wrong on filtered data
A filter hides rows; it does not remove them. Every ordinary function still reads the hidden cells, so a SUM above a filtered sales column keeps showing the grand total no matter what you filter. Analysts get caught by this daily: the filtered view shows Delhi only, the total still includes Mumbai. SUBTOTAL was built for this problem, and AGGREGATE (Excel 2010 and later) extends it with error handling and extra functions such as LARGE, SMALL and PERCENTILE.
SUBTOTAL syntax and function numbers
=SUBTOTAL(function_num, ref1, [ref2], ...)
The first argument chooses the calculation. Numbers 1 to 11 ignore rows hidden by a filter but include rows you hid manually; numbers 101 to 111 ignore both filtered and manually hidden rows. Both ranges ignore other SUBTOTAL results inside the reference, so nested subtotals never double count.
| Function | Includes manually hidden rows | Ignores manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT (numbers) | 2 | 102 |
| COUNTA (non-blank) | 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 |
Rows hidden by a filter are always ignored by every function number. The only difference between 9 and 109 is what happens to rows you hid yourself with Hide Rows.
How to total a filtered list with SUBTOTAL
- Leave a blank row above the header, or use a cell to the right of the data, so the formula is never hidden by the filter itself.
- In that cell type
=SUBTOTAL(109,E2:E100), where E holds Sales, and press Enter. Before any filter it equals the SUM. - Press Ctrl+Shift+L, filter Location to Delhi, and the SUBTOTAL drops to the Delhi total.
- For a visible-row count, use
=SUBTOTAL(103,B2:B100)on a text column such as Employee. Number 103 counts non-blank cells; 102 counts numbers only.
When you type SUBTOTAL, Excel shows the function list with the numbers, so you rarely need to memorise them. If you press Alt+= (AutoSum) inside a filtered range, Excel inserts SUBTOTAL(9, …) automatically. The Total Row of an Excel Table uses SUBTOTAL(109, …) for the same reason.
AGGREGATE syntax and options
=AGGREGATE(function_num, options, ref1, [ref2], ...) for the reference form, and =AGGREGATE(function_num, options, array, [k]) for functions that need a k value such as LARGE and SMALL.
| Option | Ignores |
|---|---|
| 0 or omitted | Nested SUBTOTAL and AGGREGATE only |
| 1 | Hidden rows and nested functions |
| 2 | Error values and nested functions |
| 3 | Hidden rows, error values and nested functions |
| 4 | Nothing |
| 5 | Hidden rows |
| 6 | Error values |
| 7 | Hidden rows and error values |
Function numbers 1 to 11 match SUBTOTAL (1 AVERAGE, 9 SUM and so on). Numbers 12 to 19 add MEDIAN (12), MODE.SNGL (13), LARGE (14), SMALL (15), PERCENTILE.INC (16), QUARTILE.INC (17), PERCENTILE.EXC (18) and QUARTILE.EXC (19).
=AGGREGATE(9,5,E2:E100) sum of visible rows
=AGGREGATE(9,7,E2:E100) sum of visible rows, errors ignored
=AGGREGATE(14,5,E2:E100,1) largest visible value
=AGGREGATE(14,5,E2:E100,2) second largest visible value
=AGGREGATE(12,5,E2:E100) median of visible rows
=AGGREGATE(1,7,E2:E100) average of visible rows, ignoring #N/ASUBTOTAL vs AGGREGATE vs SUM
| Need | SUM / COUNT | SUBTOTAL | AGGREGATE |
|---|---|---|---|
| Ignore filtered rows | No | Yes | Yes (option 1, 3, 5, 7) |
| Ignore manually hidden rows | No | Yes (101 to 111) | Yes (option 1, 3, 5, 7) |
| Ignore error values | No | No | Yes (option 2, 3, 6, 7) |
| LARGE, SMALL, MEDIAN, PERCENTILE | Separate functions | No | Yes (12 to 19) |
| Ignore nested subtotals | No | Yes | Yes |
| Works on hidden columns | Yes | No, rows only | No, rows only |
| Excel version | All | All | 2010 and later |
Worked example
The sales list below has one error caused by a lookup that found no match:
| Employee | Location | Sales |
|---|---|---|
| Ravi | Delhi | 4,200 |
| Neha | Mumbai | 5,100 |
| Amit | Delhi | #N/A |
| Sara | Delhi | 2,900 |
| Vikram | Mumbai | 3,600 |
With Location filtered to Delhi, the results in cells above the table are:
=SUM(C2:C6)returns #N/A, because SUM reads every row including the error.=SUBTOTAL(109,C2:C6)also returns #N/A: it skips hidden rows but the error row is visible.=AGGREGATE(9,7,C2:C6)returns 7,100 (4,200 + 2,900): hidden rows and errors both ignored.=SUBTOTAL(103,A2:A6)returns 3, the number of visible Delhi rows.=AGGREGATE(14,7,C2:C6,1)returns 4,200, the largest visible sale.
Using SUBTOTAL for a visible row counter
A serial number that renumbers itself after filtering is a common request. In A2 enter =SUBTOTAL(103,$B$2:B2) and fill down. Each cell counts the visible non-blank cells from the top of column B to its own row, so the numbering always runs 1, 2, 3 in the filtered view. Because SUBTOTAL formulas are recognised by AutoFilter, the last row is never hidden by mistake when you filter on this column.
Tips and common mistakes
- Put the formula outside the filtered rows. A SUBTOTAL inside the list can be hidden by the very filter it measures. Use a cell above the header or a Table Total Row.
- Use 109, not 9, when you also hide rows by hand. Number 9 still includes manually hidden rows.
- SUBTOTAL ignores hidden rows, not hidden columns. For a horizontal layout, neither function helps; use a helper row with a visibility flag.
- Errors stop SUBTOTAL. Switch to AGGREGATE with option 6 or 7 when the column may contain #N/A or #DIV/0!.
- AGGREGATE needs the array form for LARGE and SMALL. The k value goes in the fourth argument, not inside the range.
- A SUBTOTAL total in the top row counts as data. Leave a blank row between it and the header, or Excel may treat it as part of the range.
- Data > Outline > Subtotal is a different feature: it inserts SUBTOTAL formulas at each group break of a sorted list. Sort the group column first.
Errors and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| SUBTOTAL still shows the grand total after filtering | Reference points at another sheet’s range, or the filter is on a different block | Check the range matches the filtered column |
| #VALUE! from SUBTOTAL | function_num outside 1 to 11 or 101 to 111, or a 3-D reference | Use a valid number; SUBTOTAL cannot span sheets |
| #VALUE! from AGGREGATE | k missing for LARGE/SMALL, or k larger than the visible count | Add the k argument; keep k within range |
| #N/A or #DIV/0! in the total | Error in the range and SUBTOTAL used | Use AGGREGATE with option 6 or 7 |
| Count too high after hiding rows manually | Function 2 or 3 used | Use 102 or 103 |
| AGGREGATE shows #NAME? | Excel 2007 or earlier | Use SUBTOTAL; AGGREGATE needs Excel 2010 or later |
Practice exercise
Open the practice file and try the following:
- Insert two rows above the header. In the top row add
=SUM(E:E)and=SUBTOTAL(109,E:E)side by side, filter Location, and compare. - Add
=SUBTOTAL(103,B:B)-1to count visible records (minus one for the header). - Hide three rows manually, then compare
=SUBTOTAL(9,E:E)with=SUBTOTAL(109,E:E). - Type
=NA()in one Sales cell and fix the total with=AGGREGATE(9,7,E:E). - Use
=AGGREGATE(14,5,E:E,3)to find the third largest visible sale, then change the filter and watch it update.
Key takeaways
- SUM, COUNT and AVERAGE include hidden rows; SUBTOTAL and AGGREGATE do not.
- SUBTOTAL 1 to 11 ignores filtered rows; 101 to 111 also ignores manually hidden rows.
- AGGREGATE adds error handling (options 2, 3, 6, 7) and functions 12 to 19 such as LARGE, SMALL and MEDIAN.
- Both functions ignore other SUBTOTAL or AGGREGATE results inside their range, so nested totals never double count.
- Keep the formula outside the filtered rows, or use the Total Row of an Excel Table.
Related lessons
- Sort and Filter course hub
- Filter data in Excel with AutoFilter
- Sort and filter an Excel Table, including the Total Row
- SORT, SORTBY, FILTER and UNIQUE functions
- SUMIFS function
- COUNTIF function
- Subtotals and grand totals in a pivot table
- Microsoft Support: SUBTOTAL function
Frequently asked questions
How do I sum only visible cells in Excel?
Use =SUBTOTAL(109,range) or =AGGREGATE(9,5,range). Both ignore rows hidden by a filter, and both ignore rows you hid manually. SUBTOTAL(9,range) ignores filtered rows only. Place the formula above the header or beside the data so it is never hidden by the filter itself.
What is the difference between SUBTOTAL 9 and 109?
Both skip rows hidden by a filter. Number 9 still adds rows you hid with Hide Rows, while 109 skips those too. The same rule applies across the list: 1 to 11 include manually hidden rows, 101 to 111 exclude them. Filtered rows are always excluded.
Does SUBTOTAL ignore hidden columns?
No. SUBTOTAL and AGGREGATE only test row visibility. In a horizontal layout with hidden columns they return the same value as SUM. Use a helper row with a 1 or 0 visibility flag and SUMPRODUCT instead.
Why does AGGREGATE return #VALUE!?
The usual causes are a missing k argument for LARGE, SMALL, PERCENTILE or QUARTILE, a k larger than the number of visible values, or an invalid function number. Check that the fourth argument is present for functions 14 to 19 and that the options number is between 0 and 7.
Is SUBTOTAL faster than SUMIFS?
They do different jobs. SUMIFS applies its own criteria and ignores the filter; SUBTOTAL has no criteria and follows the filter. For an interactive report where the user filters the sheet, SUBTOTAL is simpler and reacts instantly. For fixed conditions in a summary, SUMIFS is the right tool.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.