Running Total Tricks in Excel Tables

A few days ago one of our readers Micheal, asked me a question.

The original question was pertaining to running total if the data is converted into a table. I took it a bit further and added 2 interesting variations to it. These are very practical issues that you might have faced in the past.

Take a look!

 

Assume this Data…

I am going to ask you 3 questions and then also present the solutions!

• Q1 – How would you get a running total for amount. Even if you sort (any) column the running total should not break
• Q2 – Running total sorted by customer should reset when the customer name changes!
• Q3 – Keeping Q2, running total (for visible rows only) when the table is filtered

 

Solution 1

Using this formula, running totals would work fine even if you sort any of the other columns.

If you are wondering that I could have written a (much much..) simpler formula as compared to this. Yes! you are right but you’ll get the essence of this formula when we move on to the next 2 problems. But I would like to explain the formula here

1. The INDEX function is always returning the first value in the amount column
2. The [@Amount] is referring to the current row
3. The semicolon in between creates a range from the first amount value to the current row
4. And the SUM function takes the sum of that range. Pure and Simple (may be not simple, but pure for sure :D)!

 

Solution 2

It gets a little tricky here. We now have to get the running total and reset the total when the customer name changes

Let me explain this

1. The INDEX works a little differently than it was before. If the customer name changes (lets say Data Tronics) the first value returned by INDEX will also correspond to that customer (Data Tronics)
2. [@Amount] is referring to the current row
3. The SUM function wraps around this formula to give the sum.

 

Solution 3

Now the running should not only reset at the customer but should also only be displayed for visible rows, when the filter is applied

The formula might look complex but there is just a little difference

1. Everything remains the same but
2. Instead of using the SUM function I use the AGGREGATE function which is able to handle filtered rows 😎

 

Side Note : You might want to replace the SUM Function by the AGGREGATE Function everywhere since it is more robust will only consider only visible rows

 

Thank you Michael for asking an interesting question, I am sure a lot of people would be facing this challenge!

 

Some Other Formula Hacks

1. SUM Function Hacks
2. Common Date Problems and Solutions
3. 7 additional date formulas that make life easy
4. 5 Helpful Array Formulas
5. Common mistakes in VLOOKUP 
6. How to apply speed up VLOOKUP on large data
7. Learn Excel Formulas (Videos)