Excel Hints & Tips – Calculations on a filtered list

Auto filter is a wonderful thing isn’t it? 

With a few clicks of the mouse little dropdowns appear letting you filter your data in a myriad different ways.

Pic 1But 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:

Pic 2

And here’s my filtered data:

Pic 3Notice 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:

Pic 4Written by Darren Andrews, Happy Trainer


If you have any Excel questions or hints and tips you would like shared get in touch, use this link!

If you would like any further information on the Excel course we train, go to our dedicated Excel page on our website. If you are unsure of your Excel level, take our handy Excel survey.

Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access.  Call the helpline on 020 7375 7373.

Happy Computers

2 responses to “Excel Hints & Tips – Calculations on a filtered list”

  1. Since the procedures did not work for me I copied the filtered data on Word and from there back to an Excel Sheet. The usual procedures can be applied and now hidden data interfere.

    1. Please correct my comment. It should be “no hidden data interfere”

Leave a Reply

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