SUBTOTAL vs AGGREGATE in Excel: Totals That Skip Rows

SUBTOTAL vs AGGREGATE comes down to errors and extra functions. Both give totals that skip the rows a filter hides. SUBTOTAL is shorter and works in every version, but a single #N/A or #DIV/0! in the range breaks it. AGGREGATE can ignore errors and offers more calculations, such as MEDIAN, LARGE and SMALL. This guide compares them on the same data, so you can pick the right one for a report or a job test.

SUBTOTAL vs AGGREGATE at a glance#

AGGREGATE is SUBTOTAL with more functions and more control over what to skip.

How SUBTOTAL works#

SUBTOTAL runs one of 11 calculations on the visible rows of a range. The first argument is a function number:

=SUBTOTAL(9,C2:C7)

9 means SUM. Other common numbers are 1 (AVERAGE), 2 (COUNT), 3 (COUNTA), 4 (MAX) and 5 (MIN). Add 100 to skip rows you hid by hand as well as filtered rows: =SUBTOTAL(109,C2:C7). SUBTOTAL also ignores other SUBTOTAL formulas inside its range, so group subtotals aren't counted twice.

The SUBTOTAL function guide lists all 11 numbers. For how it compares with a plain SUM, see SUBTOTAL vs SUM.

How AGGREGATE works#

AGGREGATE has the same idea plus a second argument, options, which says what to skip:

=AGGREGATE(function_num, options, ref1, ...)

Functions 1 to 13 match SUBTOTAL's list and add 12 (MEDIAN) and 13 (MODE.SNGL). Functions 14 to 19 are LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC. They take an extra last argument, k: =AGGREGATE(14,6,C2:C7,2) returns the second-largest value.

Function numbers side by side#

Both functions share the first 11 numbers, so 9 means SUM in either one. Only AGGREGATE has 12 to 19.

In AGGREGATE, the 100-series numbers don't exist. Hidden rows are controlled by the options argument instead.

The same data solved both ways#

Here is a small sales list. Chloe's cell shows #DIV/0! because the formula behind it divides by an empty cell.

Task 1: total everything, despite the error#

=SUBTOTAL(9,C2:C7)
=AGGREGATE(9,6,C2:C7)

SUBTOTAL returns #DIV/0!, just like =SUM(C2:C7). AGGREGATE returns 1,600 (400 + 250 + 300 + 150 + 500), because option 6 ignores errors.

Task 2: filter to South#

Filter the Region column to South (rows 3 and 6). Now the error row is hidden:

=SUBTOTAL(9,C2:C7)
=AGGREGATE(9,5,C2:C7)

Both return 400 (250 + 150). SUBTOTAL works again because the cell with the error is no longer visible.

Task 3: filter to North#

Filter to North (rows 2, 4 and 7). Chloe's error is visible again:

=SUBTOTAL(9,C2:C7)
=AGGREGATE(9,7,C2:C7)

SUBTOTAL returns #DIV/0!. AGGREGATE with option 7, which skips hidden rows and errors, returns 900 (400 + 500).

Task 4: the second-largest sale#

SUBTOTAL has no LARGE function, so it can't answer this. With the filter cleared:

=AGGREGATE(14,6,C2:C7,2)

The result is 400: the largest is 500, then 400. Use option 7 instead of 6 to rank only the visible rows.

Task 5: rows hidden by hand#

Clear the filter, then hide row 5 (Dev) by right-clicking its row number and choosing Hide:

=SUBTOTAL(109,C2:C7)
=AGGREGATE(9,7,C2:C7)

SUBTOTAL still returns #DIV/0!, because Chloe's row is visible. AGGREGATE returns 1,300: the 1,600 total minus Dev's 300. With =SUBTOTAL(9,C2:C7) and no error, Dev's 300 would still be counted, because 9 only skips rows hidden by a filter.

When to use SUBTOTAL#

Use SUBTOTAL when the data is clean and you only need totals, counts or averages of visible rows. It fits well when:

• You filter a list and want the total to match the screen. The guide to summing only visible cells covers this case.
• You use a table's Total Row or Data > Subtotal. Excel writes SUBTOTAL formulas for you there.
• The file must open in very old Excel, before 2010.
• Other people maintain the file. SUBTOTAL is more widely known, and =SUBTOTAL(109,C2:C7) is easy to read.

When to use AGGREGATE#

Use AGGREGATE when errors might appear, or when you need a calculation SUBTOTAL doesn't have. It is the better choice when:

• The data has errors you can't fix yet, such as #N/A from a lookup on imported data. See how to sum and ignore errors for other approaches.
• You need MEDIAN, LARGE or SMALL on filtered rows, such as the top sale in the visible region.
• You need a conditional top value that skips errors. In the array form, =AGGREGATE(14,6,C2:C7/(A2:A7="North"),1) returns the largest North sale, 500. Rows that aren't North become errors when divided by FALSE, and option 6 skips them along with Chloe's #DIV/0!. This form works only with functions 14 to 19.

Common mistakes with SUBTOTAL and AGGREGATE#

Most wrong results come from the function number or the option:

1. Using AGGREGATE option 6 on a filtered list. Option 6 skips errors but still includes filtered rows. Use 7 to skip both.
2. Mixing up the arguments. In AGGREGATE, the function number comes first and the option second. =AGGREGATE(6,9,C2:C7) means PRODUCT with an invalid option, and Excel returns #VALUE!.
3. Using SUBTOTAL 9 after hiding rows by hand. 9 only skips filtered rows. Use 109.
4. Expecting hidden columns to be skipped. Both functions skip hidden rows only. A horizontal range still includes hidden columns.
5. Hiding errors you should fix. Skipping a #N/A means that row's value is missing from the total. Check why the error is there.

For the filter steps themselves, see how to filter data in Excel, and for SUBTOTAL's group-total uses, the SUBTOTAL function in Excel.

Our recommendation#

Use SUBTOTAL, usually with 109, for clean lists that people filter: it is short, familiar and built into tables. Switch to AGGREGATE when the range might contain errors, or when you need MEDIAN, LARGE or SMALL on visible rows. On a filtered list with possible errors, =AGGREGATE(9,7,range) is the safest total. And for fixed totals with no filters or errors, a plain SUM is still the simplest choice.

Practise it#

Totals that follow a filter are a common Excel test task. Practise filtering a table in a live spreadsheet: you'll filter by value and condition, then total and count only the visible rows with SUBTOTAL. If you need a refresher first, practise SUM and AutoSum. Every answer is checked the moment you press Enter. Or try the AGGREGATE exercise on this page.