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:

10 VAR

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

4 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”

  2. David Morris - Data Manager

    Darren – I want to thank you profusely for this hint. I am surprised you have not received more comments – perhaps because people wouldn’t think it was possible. I know someone who will be very pleased when I share this with him.

  3. thanks dude – that saved me a fair bit of cussing (and drinking) – appreciate it!

Leave a Reply

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