How To Use The AGGREGATE Function with Hidden Rows and Errors In Excel

The AGGREGATE function in Excel is much more powerful than the SUBTOTAL function with additional built in options and the ability to ignore errors and hidden rows. Let’s see how that works in Excel!

So here I have a data set and I want to get the SUM and AVERAGE of the Sell Price Value from column “I”:

I can simply use the SUM and AVERAGE functions to do so, and it will give me the correct answers:

The problem might be that when I filter the data by New Orleans, the values remain the same. It still sums and averages the entire column, not just the visible cells:

Now this can be easily corrected by the use of the SUBTOTAL function, but we are going to use the AGGREGATE function, since it has a few more additional uses and benefits. The AGGREGATE function offers 19 different functions:

…and 7 different behaviors:

The syntax for the AGGREGATE function is:

=AGGREGATE(function_num,options, array,[k])

So if we choose to ignore hidden rows, our formulas would be:

SUM:             =AGGREGATE(9,5,\$I\$7:\$I\$102)
AVERAGE:  =AGGREGATE(1,5,\$I\$7:\$I\$102)

For the function_num, the 9 is for SUM and the 1 is for AVERAGE, and the option of 5 means ignore the hidden rows.

Now, when we filter by New Orleans, the formulas only SUM and AVERAGE the visible data:

However, look what happens when we have an error in our range:

The solution here is to change the option from 5 to 7, which ignores hidden rows AND errors. So when we filter the data by New Orleans, even though we have an error, the AGGREGATE function will still SUM and AVERAGE the visible data and ignore the error:

What can you do next?
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!