Accumulating Visible Cells
Craig, a regular reader here, asked a cool question. He has a list of numbers where one column is a cumulative total, similar to this
The last column is an accumulation of the second column. The problem is when the data is filtered. The cumulative column still shows the same accumulation, not just the accumulation of visible cells. The SUBTOTAL worksheet function works with visible cells, so that seems like a nice solution to this problem.
Now when the data is filtered, the cumulative column is updated to reflect the proper amounts.
I did not know that.
Daily Dose of Excel: Accumulating Visible Cells. Basically how to make Excel add up only the cells that you have the current filter set to display. Neat. Anyway… I teach…
Thanks for all the great tips and suggestions. They have been very enlightening, to say the least!
I did have a minor problem with this latest tip. I’m still in the Excel 97 world and that might be the problem. When I entered the data and applied the filter, the last data item always remained, whether it satisfied the filter requirement or not. In your example, you filtered by what was the last item in the list, so it didn’t make a difference.
Just wondering if it’s me, or Excel, or simply the version I’m working with.
Thanks again for all the great advice.
Well I’ll be horsewhipped. That happens to me too. Time for a little investigation.
It would appears that the last row is displayed due to the presence of the SUBTOTAL function. Myrna Larson provided an explanation and workaround
http://groups.google.com/groups?selm=36dd92d6.14804282%40msnews.microsoft.com
Thanks, Andy. Adding the dummy row resolved the problem.
I found that same thread last night. I’ve got a post coming later today thats even BETTER than a dummy row. But you’ll have to wait to read it.