How to sum values in a filtered list???

General December 3rd, 2007

Once we have applied filter to our data using the excel’s “AutoFilter” option and we want to sum only the non-hidden range, then we can’t use the simple “sum” function as it adds both the hidden and visible cells. In that case we can use the excel’s subtotal function as it only sums the visible range.

The syntax of the Subtotal function is:

SUBTOTAL(function_num,range_reference1, range_reference2….)

We can perform 11 different functions in conjunction with subtotal.

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

Suppose you have a list of 4 different products - A, B, C and D along with their quantity sold.

subtotal1.JPG

And you wish to add up only the quantity sold of product A. Then if you apply filter and sum the range using “Sum” function then it will give the total for all the products

subtotal2.JPG

Whereas if you use subtotal function you will only get it for product A as shown below:

subtotal3.JPG

Share This

Popularity: 32%


Leave a comment Comment RSS

Previous: Converting Text to Columns in Excel
Next: Selecting non-adjacent cells in excel

Reader's Comments

  1. Mike | January 3rd, 2009 at 9:46 pm

    Great dude THANKS A LOTSSS was searching for this high and low….

  2. Antonio | February 16th, 2009 at 4:24 pm

    Great, Thanks for the info! It works great!!

  3. Yen Nguyen | May 7th, 2009 at 10:47 am

    Thanks a lot for the tip of using subtotal. This gave me a great help!!!

  4. Rakib Hasan | August 7th, 2009 at 7:46 am

    Gr8! Highly benefited.

    Thnx a lot!

  5. Aditya | August 17th, 2009 at 11:01 am

    Great & Thank You Very Much.

  6. Reji | August 25th, 2009 at 1:01 pm

    Thanks for the tip, it proved handy.

  7. Moisés Briseño Estrello | September 17th, 2009 at 6:11 pm

    Excellent tutorial, very very useful. Thanks!!!

  8. Kristen | September 25th, 2009 at 4:02 pm

    Wow, I searched multiple sites for this info. Yours is the most helpful and definitely the easiest to understand. Thank you!

  9. Ramesh | October 15th, 2009 at 9:27 am

    Thanks a lot for this tip… this really helped me a lot ..

  10. iceman | January 2nd, 2010 at 4:49 am

    This is nice

  11. KRISS | January 14th, 2010 at 6:25 pm

    Thank you for filling in one of many gaps in Microsoft’s “help” information.

  12. Matthew | March 25th, 2010 at 5:31 pm

    Awesome… very helpful, thanks!

  13. C.Lozes | May 3rd, 2010 at 5:23 pm

    Thanks -really helpful

  14. KK | November 16th, 2010 at 12:27 am

    Thanks for the tip. Was of great help to me and continues to be.

  15. Don | December 7th, 2010 at 10:39 pm

    THANKS!!!

Leave a Comment

Close
E-mail It