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.


Posted in Uncategorized

6 thoughts on “Accumulating Visible Cells

  1. 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…

  2. 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.

  3. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.