We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention to the filters. Let’s learn about them.
Summarize filtered values using SUBTOTAL & AGGREGATE Formulas – video
You may watch this video on our YouTube channel too.
Download Example Workbook
Please click here to download the example workbook. Examine the formulas and play with filters to learn more.
More about Filters, SUBTOTAL & AGGREGATE
Learn more about these features & how they can make you awesome:
Basics:
- Introduction to SUBTOTAL Formula
- Introduction to Excel Slicers – Visual Filters
- Make dynamic charts with filters
Advanced Stuff:
- How to count & sum filtered tables,
- Count & sum with criteria on a filtered list
- Sum up top 3 filtered values using AGGREGATE
- Check if a table is filtered or not using formulas
- Case study – Christmas shopping list
What do you use SUBTOTAL for?
Do you use SUBTOTAL and AGGREGATE functions? What do you use them for? Please share your thoughts in the comments section.
4 Responses to “Summarize only filtered values using SUBTOTAL & AGGREGATE formulas”
Thanks Chandoo - once again you have inspired me to look at a different function for something I do regularly.
I have used the Subtotal function for years, but had completely ignored Aggregate - and this showed me that I really need to look at this function more closely.
I use Subtotal for filtering purposes, but also to provide subtotals of my numbers and know that they can be ignored when I use a Subtotal at the very bottom to sum the entire column.
Thanks, Chandoo, I am very happy to know this new function - aggregate, it make such a compensate for the subtotal...great...
I use SUBTOTAL(103,Ref) to determine whether a row is hidden or not.
Here's an example:
http://wmfexcel.com/2015/08/22/put-a-dynamic-hint-for-showing-or-hiding-rows-or-columns-hidden-by-data-group/
superb! its a great help.