Excel "Dynamic SUM" when Grouping / Hiding Data....?

Posted on 2011-10-10
Medium Priority
Last Modified: 2012-05-12
I have columns of data that have a SUM TOTAL "ie..SUM(a10:a2000)"

I would like to filter on a column and then have this TOTAL SUM ONLY THE FILTERED ITEMS....

How do i do that.....?

As it stands now.,..when I filter the Total for ALL rows is still showing in the total cell. What syntax or code do I use in the total cell so that it will ONLY summarize the FILTERED data rows..?

Question by:MIKE
LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 36945204
Use SUBTOTAL function to sum with visible data only


9 indicates SUM - there are other numbers for different functions like AVERAGE, MIN, MAX etc. see SUBTOTAL help for more....

regards, barry

Assisted Solution

J79123 earned 1000 total points
ID: 36945230
it's called the subtotal function.
the syntax is subtotal (#, range)
where # is the magic number of what type of subtotal you want. Sum, average etc. The number for summing is 9.
so if I wanted to sum a1:a10
I could put in cell say a12
=subtotal (9, a1:a10)
then if i add a filter and end up hiding a few rows, that total in a12 will change accordingly.

or option 2... you can add some auto filters to your data first. Then choose an option on the filter so that it ends up hiding some cells.
Next at the bottom of the column you want to sum, click the sum button and select the first row to the end of the column (even though some rows are hidden)... Excel knows that you have hidden rows it will create a subtotal formula instead of a sum formula.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question