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

10 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!

  4. This is a cool hint. Do you know how to get it to work with statistical functions like quartiles?

  5. I want the filtered sum of a data set, but I don’t want the total reverting back once I unfilter. Is there a function or term that allows you to keep the filtered sum when I unfilter the data range?

    1. Hi Nunyah,

      Yes, there is.

      Simply by creating your data set into “Excel Table” where you will find “Format As Table” from the Home tab on the ribbon. Alternatively, select the whole data set within table by using keyboard shortcut, CTRL + A and select one of the “Format As Table” style and select “OK” from its dialog box. The data set is now formatted as “Excel Table”. Within its table, you will find contextual (additional) tab, “Design” tab under “Table Tools” next to “View” tab. Apply the checkbox (tick) next to “Total Row” under the group name “Table Style Options”. This will add the Total Row at the bottom of its table. Select a blank cell under which column you wish to calculate its formula. You will find SUM, AVERAGE, MAX, MIN functions etc. Now apply the filter as you wish, it calculates based on filtered option. Once you have applied the filtered option with its SUM calculation. You can now copy and paste as Values, by right clicking on the SUM function to copy and right click to the cell where you wish to paste using “Values 123”, usually the second icon on the left under Paste Options when right clicking. This will store its values after you unfilter the data range.

      🙂

      Best wishes,
      Claire

  6. hey this is very useful hint but doesn’t seem to work for me, I have a column of time for which i need to calculate min,max etc based on the months. Subtotal is giving me miscellaneous values.:(

    1. Hi Aish

      Thank you for your question. The reason you’re struggling to use the MIN and MAX functions on your dates is because of the “underlying” data that is a date. Dates in Excel are just serial numbers, for instance if you enter 34648 into a cell and format it as a date you can see when I got married. Because the MAX and MIN are statistical functions they are unable to identify the different “Parts” of the date serial number and just treat it as one big number.

      For many types of analysis it is necessary to separately identify months, years even days, to do this you may need to use some date functions. In the screen shots below I’ve used the MONTH function in column B:B and the year function in column C:C. With the month separated you should be able to calculate the MAX month.

      Best wishes

      Claire

  7. I needed to work with applying various formulas for filtered data, so this was a life-saver – just what I was looking for. Thank you!

Leave a Reply

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