Cumulative Total in a Filtered Excel List

A solution to a tricky problem

You might think adding a cumulative total to a filtered list is impossible, but with one of Excel’s new functions, it is easy.

Now I must admit I did think I could use the SUBTOTAL function to solve this, but it had a weird affect that I will share with you at the end of the post.

You can’t use SUM because it adds up all the values in a range, whether they are visible or not.

AGGREGATE Solution

THE AGGREGATE function is a new function added in Excel 2010. Like SUBTOTAL it can ignore hidden rows. Unlike SUBTOTAL and other Excel functions, it can ignore error values. It can also solve our problem.

The image below has our list.

List

We want to populate column D with a cumulative total that will work when the sheet is filtered by the Department in column B.

The formula for cell D2 is

=AGGREGATE(9,5,$C$1:C2)

This formula can be copied down. The 9 in the function means perform a SUM calculation and the 5 means ignore hidden rows.

The start of the range $A$1 is fixed to the header row so that sorting won’t affect it. The end of the range is relative so it will always SUM up to the row it is used on.

Filt_02
When the list is filtered, the formula still works.

Filt_03

SUBTOTAL Problem

Initially I thought I could use the SUBTOTAL function, but it caused an error that I had not seen before. SUBTOTAL can also ignore hidden rows.

When you add the SUBTOTAL function to the list it affects the filter in the other column. See below for the result using SUBTOTAL.

The formula in cell D2 is

=SUBTOTAL(109,$C$1:C2)

Filt_04

Even though I had filtered for Admin it still displays the last row of the table and note that row 17 is not blue like the other rows.

This even happened with a formatted table, which amazed me. This may be a bug.

You can download the example file including both examples from the link below.

Cumulative_Filter

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Cumulative Total in a Filtered Excel List

  1. I am trying to get a cumulative subtotal in a spreadsheet as I enter the lines. There are filtered rows in this spreadsheet. Using the cell above to subtract another cell to get the running balance.