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.
aggregate01

More Functions in AGGREGATE

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

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.
aggregate03

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.
___________