Sum a Filtered List with AGGREGATE Function

The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.

A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.


More Functions in AGGREGATE

The first advantage is that AGGREGATE has 19 functions, compared to SUBTOTAL's 11 functions.


Ignore Errors with AGGREGATE

Another advantage is that AGGREGATE can ignore errors, as well as hidden rows. You can choose from a list of options, for what to ignore.


Watch the Video

Watch this short video to see the steps for setting up an AGGREGATE total, and see how it differs from SUM and SUBTOTAL


Download the Sample File

To download the sample file, please visit the Excel Sum Functions Examples page on my Contextures website.


You may also like...

16 Responses

  1. Sumit Bansal says:

    Thanks Debra.. This is really useful!!

  2. JoAnn Paules says:

    This is good stuff. Is there a way to use AGGREGATE instead of a total in a pivot table?

    • @JoAnn Paules, not yet, but it would be great to have all those functions available in a pivot table too.

      • derek says:

        I would love to able to use percentiles in a pivot table!

        What an odd order those eight ignore codes are. In binary terms, it ought to start with ignore nothing and end with ignore everything, or go the other way. This seems to be shifted four spaces modulo eight.

  3. OmarF says:

    I’m not sure how I missed this. It’s only been out there for 6 years now!

  4. ruve1k says:

    The AGGREGATE function only became available in Excel 2010 — not Excel 2007.

  5. indzara says:

    Helpful. Thank you.

  6. Bob Watson says:


    Nice article, very helpful.

    One feature I don’t like about SUBTOTAL and AGGREGATE is the use of constants to represent parameters. That is, the formula =AGGREGATE(9,3,D2:D7) is difficult to understand because the 9 and 3 have no intrinsic meaning. Instead, I define named ranges to represent the parameters I want to use. For example, I define a named range called “Sum” with a value of 9, and another named range called “IgnoreAll” with a value of 3. Then the formula would be =AGGREGATE(IgnoreAll,Sum,D2:D7).



  7. Anne says:

    Haven’t come across it before – but that’s a rather excellent function. Pity they don’t it available for pivot tables. I’ve been asked more than once about using a median in a pivot table and I’ve usually sent them over to your solution :-) Thanks for excellent video – really well explained and clear as usual. Anne

  8. Anne says:

    @Bob – really like that idea. So true – people don’t remember what 9 – nice solution..

  9. NLee says:

    very useful, thanks Debra

  10. Neranjan says:

    Thanks !!!

  11. Lynda Maynard says:

    now if they could just add “COUNTIF” and “SUMIF” to that list…

  1. November 16, 2017

    […] you have Excel 2010 or later, the AGGREGATE function is a more powerful version of […]

Leave a Reply

Your email address will not be published. Required fields are marked *