Quick Links
For many years, I used the SUBTOTAL function in Microsoft Excel to create easily visible subtotals at the top of my worksheets. However, when I encountered AGGREGATE, this became my go-to function for achieving the same result but with more flexibility.
Before I explain further, let’s remind ourselves how the SUBTOTAL function works.
The SUBTOTAL Function
Microsoft Excel’s SUBTOTAL function is quite simple to use. It lets you perform subtotal calculations for ranges of cells, and you may decide whether you want to include manually hidden rows in the result.
However, rows that are filtered out are always excluded, and the SUBTOTAL function doesn’t work if there are errors in the data, unless you nest it within theIFERROR function. What’s more, SUBTOTAL only supports 11 functions, limiting its use if you want to perform more complex statistical calculations.
where
For argumenta, 1 or 101 = AVERAGE, 2 or 102 = COUNT, 3 or 103 = COUNTA, 4 or 104 = MAX, 5 or 105 = MIN, 6 or 106 = PRODUCT, 7 or 107 = STDEV, 8 or 108 = STDEV.P, 9 or 109 = SUM, 10 or 110 = VAR, and 11 or 111 = VARP.
Numbers 1 to 11 for argumentaforce the result to include manually hidden rows, while 101 to 111 exclude manually hidden rows.
In this example, the SUBTOTAL function has been used to calculate the average of the values in column B, with row 6 (team E) hidden. The subtotal in cell E1 includes the hidden row (argumenta= 1), while the subtotal in cell E2 doesn’t (argumenta= 101).
However, when the filter is used to hide the score of team E instead, both subtotals are the same, as there’s no way to get the SUBTOTAL to include filtered-out values.
In this final example, you can see that the SUBTOTAL function returns an error if there’s an error in the range.
To avoid these issues, you’re able to use the AGGREGATE function instead.
The AGGREGATE Function
The AGGREGATE function is similar to the SUBTOTAL function, but there are more options for the type of aggregation you want to create. Also, you have a wider choice of things you’re able to exclude from the result, and you can make Excel ignore errors in your data.
In other words, compared to the SUBTOTAL function, AGGREGATE is more powerful and flexible.
The AGGREGATE function has two syntaxes—one for references and one for arrays—though you don’t need to worry about which one you’re using, as Excel selects the relevant one depending on the arguments you input.
The syntax for the reference form of the AGGREGATE function is:
If you’re working with arrays, the AGGREGATE function follows a slightly different syntax:
The first difference to note between SUBTOTAL and AGGREGATE is that the latter has more options for argumenta: 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN, 6 = PRODUCT, 7 = STDEV.S, 8 = STDEV.P, 9 = SUM, 10 = VAR.S, 11 = VAR.P, 12 = MEDIAN, 13 = MODE.SNGL, 14 = LARGE, 15 = SMALL, 16 = PERCENTILE.INC, 17 = QUARTILE.INC, 18 = PERCENTILE.EXC, and 19 = QUARTILE.EXC.
However, where AGGREGATE really trumps SUBTOTAL is in its ability to exclude certain values from the result (argumentb):
0
Nested SUBTOTAL and AGGREGATE functions
1
Hidden rows, and nested SUBTOTAL and AGGREGATE functions
2
Errors, and nested SUBTOTAL and AGGREGATE functions
3
Hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
4
5
Hidden rows only
6
Errors only
7
Hidden rows and errors
In this example, typing:
into cell E2 returns the average (argumenta= 1) of the visible values in column B, ignoring the hidden value in row 8 and the error in row 7 (argumentb= 7).
You wouldn’t be able to get the same result using the SUBTOTAL function alone in this scenario, as it can’t overlook errors without being nested within an IFERROR formula.
Even though bypassing errors is a good way to make your spreadsheet look tidier, don’t get into the habit of ignoring them altogether! They’re there for a reason and could help with troubleshooting.
On the other hand, typing:
into cell E3 returns the average (argumenta= 1) of all the values in column B, including the value on row 8 that is filtered out and excluding the error (argumentb= 6).
Once again, this wouldn’t be possible with SUBTOTAL, as it always includes filtered-out rows in the result, and there’s no way to bypass the error in the data.
This time, in cell E3, the AGGREGATE function is used to generate the median (argumenta= 12), one of the many calculations the SUBTOTAL function doesn’t allow:
In a final example, argumentdhas been used to generate the second-largest value:
Here’s a summary of Microsoft Excel’s SUBTOTAL and AGGREGATE functions:
Suitable for straightforward aggregations
Suitable for complex statistical aggregations
Number of functions supported
11
19
Can include or exclude manually hidden rows
Can include or exclude filtered rows
Can ignore errors
Can ignore nested SUBTOTAL or AGGREGATE results
Offers further options for array functions
N
Y
Even if you think you’ve settled on a function that works for you, always double-check that there isn’t another function that does the job even better! Microsoft often introduces new functions that are upgrades from older ones—for example, I nowuse XLOOKUP instead of VLOOKUP, andXMATCH is much more user-friendly than MATCH.
