When an Excel list is filtered, how can you show a total sum or count for numbers in the visible rows only? There are two Excel functions for that task — SUBTOTAL (all Excel versions) and AGGREGATE (Excel 2010 and later). See how these functions work, and decide which one to use.
Excel SUBTOTAL Function
Should you use SUBTOTAL or AGGREGATE to get a total for filtered data?
There’s a video further down the page, where I show both functions, along with the Excel SUM function. That might help you decide which function will work best for you.
Here are a couple of reasons to choose the SUBTOTAL function:
Old Versions of Excel: If there’s any chance that you’ll have to share the file with someone who’s using Excel 2007 or earlier, go with the SUBTOTAL function. Yes, those are really old versions of Excel, but some people haven’t upgraded.
Easy to Use: It’s easy to insert the SUBTOTAL function below a filtered list:
- Select the blank cell immediately below the column of numbers
- On the Excel Ribbon’s Home tab, click the AutoSum button.
- Excel automatically inserts a SUBTOTAL formula for you, with the Sum function (9) selected.
- NOTE: In newer versions of Excel, function 109 is automatically selected, so change that to 9, if you need compatibility with older Excel versions
Excel AGGREGATE Function
If you don’t have to share the Excel file with anyone who’s using Excel 2007 or earlier, I’d recommend using the AGGREGATE function.
Here are a couple of reasons to choose AGGREGATE:
- It has 19 functions, compared to the 11 functions in old versions of SUBTOTAL
- There are 8 options for what to ignore, compared to the 2 options in SUBTOTAL
Video: Sum Filtered Excel Data
This short video shows how to sum filtered numbers in Excel, with the AGGREGATE function, or the Excel SUBTOTAL function.
You’ll also see the differences between those two functions, and how they compare to the SUM function.
More Info
Filtered Rows Count/Sum Formula Examples
Sum a Filtered List with AGGREGATE Function
_______________________
How to Sum Filtered Excel Data – AGGREGATE or SUBTOTAL
_______________________
In the old days I remembered these codes for subtotal. Now, I never do…have to swear to myself, then lookup what number to enter. I don’t understand why Microsoft coders can figure out how to may a plain English descriptor so that I don’t have to spend an extra couple of minutes pondering codes. I don’t use Excel every day anymore, sub SUBTOTAL functions are always useful.
And the AGGREGATE function has 19 codes!
I only have Average (1) and Sum (9) memorized 😉