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