Excel SUBTOTAL and AGGREGATE: totals that follow your filter

In short: =SUBTOTAL(109,C2:C50) sums only visible rows, leaving out filtered and manually hidden ones. =SUBTOTAL(9,C2:C50) leaves out filtered rows only. =AGGREGATE(9,6,C2:C50) sums while ignoring errors such as #N/A.

Try it now

A list of travel costs is going to be filtered by department, and the average below it should always match what is on screen.

Task Below a filtered list you want the average travel cost, and it should change with the filter. In D10, use SUBTOTAL with the code for average that skips hidden rows.

	A	B	C	D
1	Employee	Department	Trip	Cost $
2	Weber	Sales	Hamburg	412.5
3	Kaya	Purchasing	Cologne	186
4	Novak	Sales	Vienna	640.8
5	Weber	Sales	Bremen	95.4
6	Schulz	IT	Munich	328
7	Kaya	Purchasing	Leipzig	271.3
8	Novak	IT	Zurich	504
9
10	Avg visible

The SUBTOTAL formula

=SUBTOTAL(function_num,ref1,[ref2],…). The first number picks the calculation, and one or more ranges follow.

Includes hidden rows	Ignores hidden rows	Function
1	101	AVERAGE
2	102	COUNT (numbers)
3	103	COUNTA (non-empty cells)
4	104	MAX
5	105	MIN
6	106	PRODUCT
7, 8	107, 108	STDEV, STDEVP
9	109	SUM
10, 11	110, 111	VAR, VARP

SUBTOTAL 9 vs 109

Both versions skip rows that a filter hides. The difference is rows you hide yourself (right-click the row number, Hide): code 9 still counts them, code 109 leaves them out. So if you want to sum visible cells only, use 109. The total row of an Excel table writes exactly this kind of SUBTOTAL formula with 109.

SUBTOTAL has a second trick. If the range already contains SUBTOTAL formulas, say a subtotal per department, an outer SUBTOTAL skips them. Nothing is counted twice, so the grand total can run down the whole column.

Sum filtered data and count colored cells

Apply a filter, then put =SUBTOTAL(109,C2:C50) below the list for the total and =SUBTOTAL(103,A2:A50) for the number of visible rows. Both update the moment you change the filter.

Excel has no worksheet function that counts cells by fill color. The filter gets you there anyway: open the column's filter menu, choose Filter by Color and pick the color. SUBTOTAL with 103 then counts the colored rows, and with 109 it sums them.

The Subtotal command

For a subtotal per group, sort the list by the column you want to group on, then go to the Data tab, Outline group, and click Subtotal. Excel inserts a SUBTOTAL row at each change in that column and adds outline buttons beside the row numbers so you can collapse the list to just the totals. The command is grayed out inside an Excel table. Use the total row there, or convert the table to a normal range first.

AGGREGATE: SUBTOTAL with options

=AGGREGATE(function_num,options,ref1,…) knows 19 functions and lets you choose what to skip. Functions 1 to 11 match SUBTOTAL (1 AVERAGE, 4 MAX, 5 MIN, 9 SUM), and the extra ones include 12 MEDIAN, 14 LARGE, 15 SMALL and 16 PERCENTILE.INC. The options:

Option	AGGREGATE ignores
0 or omitted	nested SUBTOTAL and AGGREGATE results
1	hidden rows and nested results
2	error values and nested results
3	hidden rows, error values and nested results
4	nothing
5	hidden rows
6	error values
7	hidden rows and error values

The classic use: a column picks up a few #N/A values from a VLOOKUP, and SUM turns into #N/A as well. =AGGREGATE(9,6,C2:C50) adds up everything else. Functions 14 to 19 need a final k argument, such as 1 for the largest or smallest value, just like LARGE and SMALL. They also accept a calculation in place of a range.

Practice: ignore errors with AGGREGATE

Two cancelled orders have a quantity of 0, so amount divided by quantity gives #DIV/0! in those rows. You still need the lowest unit price.

Task Unit price is amount divided by quantity. Two orders were cancelled and have a quantity of 0, so dividing there gives #DIV/0!. In C10, find the lowest unit price and let AGGREGATE skip the errors.

	A	B	C
1	Supplier	Qty	Amount $
2	Nordwerk	400	948
3	Elbe Valley Inc	0	0
4	Kaya Ltd	250	562.5
5	Saxon Build	600	1338
6	Harbor Logistics	0	0
7	Weber & Sons	150	361.5
8	Spree Media	320	726.4
9
10	Lowest unit price

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Common mistakes

Hidden columns still count

SUBTOTAL and AGGREGATE are built for vertical ranges. Hiding a column does not change a horizontal total, even with 109.

#VALUE! from AGGREGATE

Functions 14 to 19 return #VALUE! when the k argument is missing. 3-D references across several sheets also give #VALUE!, in SUBTOTAL as well.

Hidden rows are not skipped in a calculation

When the range is a calculation such as C2:C8/B2:B8, Microsoft notes that the options no longer skip hidden rows or nested results. Option 6 still steps over the errors. If you just need a replacement value for an error, IFERROR is simpler.

SUBTOTAL and AGGREGATE in German Excel

English	German
=SUBTOTAL(109,C2:C50)	=TEILERGEBNIS(109;C2:C50)
=AGGREGATE(9,6,C2:C50)	=AGGREGAT(9;6;C2:C50)

SUBTOTAL in Google Sheets

Google Sheets has SUBTOTAL too, with the same codes 1 to 11 and 101 to 111, so =SUBTOTAL(109,C2:C50) carries over unchanged. Google's help says that rows hidden by a filter are never included, whichever code you use, which matches Excel.

Which Excel versions have SUBTOTAL and AGGREGATE?

Microsoft lists both for Microsoft 365, Excel 2024, 2021, 2019 and 2016, including the Mac versions. AGGREGATE is also listed for Excel for the web.

FAQ