Example of auto-filter

But what if you want to find out the total or average of your filtered data? Unfortunately the SUM, AVERAGE, COUNT, COUNTA etc functions seem to let us down here.

Here’s my unfiltered data and the SUM function in action:

Unfiltered data

And here’s my filtered data:

Filtered data

Notice anything different? No? Exactly! We may want the SUM function to only sum the visible data but, as it was designed to do, it will sum ALL of the data, even the hidden rows.

So what’s the answer? The SUBTOTAL function!

The subtotal function can, if we ask it nicely, ignore any values hidden by the autofilter. It looks like this:

=SUBTOTAL(function_num,ref1,[ref2], . . . . ])

The function_num argument tells the subtotal function what sort of calculation you want it to do:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

So here’s the same example as above using the subtotal function:

Filtered subtotal

Related Blogs

• Save Time in Excel with Autofill – Learn how to use the useful tool Autofill. Nicky explains how in the two minute video.
• How Microsoft Excel Can Increase Your Productivity – Billy talks about some of the reasons why Microsoft Excel is so indispensable to productive workplaces in this blog.
• How to Create a Pivot Table in Excel – Pivot tables are the perfect way to sort and analyse data in Excel. This two minute video takes you through the process of how to do so.