Bottom Line: Learn an easy way to calculate running totals in Excel Tables, and how to calculate running totals based on a condition or criteria.
Skill Level: Intermediate
Download the Excel File
You can download the Excel file to follow along and practice writing your own formulas for running totals.
Running Totals in Excel Tables
Paul, a member of our Elevate Excel Training Program, posted a great question in the Community Forum. He wanted to know the best way to create running totals in Excel Tables, since there are multiple ways to go about it.
The running total calculation sums all of the values in a column from the current row the formula is in to the first row in the data set.
Therefore, we need to create a range reference that always starts at the first row in the column, down to the current row the formula is in.
When using Excel Tables, it's best to use structured references (references to the table and column names) for running totals. Using regular cell references like ($A$2:A3) can cause errors in your formulas, more on that below.
Unfortunately, there is no straightforward way to create this reference with Table formulas. There are still many ways to solve this problem and I'll share three methods in this post.
Method #1: Reference the Header Cell
My preferred method is to reference the header cell to create the absolute reference for the first cell in the range. Then reference the cell in the row that the formula is in for the last cell in the range. Here is an example.
The reason I reference the header cell (tblSales[[#Headers],[Amount]]) is because the reference will NOT change as the formula is copied down. This makes it an absolute reference.
The ending reference [@Amount] creates a relative reference to the row that the formula is in.
In everyday terms, you've just told Excel to add up everything from the beginning of the Amount column (including the header row) down to the row the formula is in, and to return that value in the Running Total column.
Because we are using an Excel Table, the formula will automatically be copied down the entire column. You can see how each cell adds the current amount to the existing total to give a running total.
Tips for Writing the Formula
- When writing this formula you can click the header cell to create the reference (tblSales[[#Headers],[Amount]]). You do NOT have to type out the entire reference.
- After clicking the header cell, click into the end of the formula to edit it and type a : colon. Then select the cell in the row the formula is in to create the ending reference [@Amount].
A Few Notes About the References
There are a few good things to know about this running total formula with structured references.
- The header cell always contains the text data type. Therefore, the value in the header cell is NOT included in the SUM formula calculation. Even if the header cell contains a number, that number is stored as text and will not be included in the calculation.
- When looking at the formula in Edit mode, only the cells in the header row and current row are selected. Excel does put a colored border around the entire range. This does not impact the results of the formula. All of the cells between the header cell and current row are included in the calculation. It is just a visual nuance to be aware of.
Why Use Structured References?
You may be wondering why I use structured references (tblSales[[#Headers],[Amount]]:[@Amount]]) instead of regular range references ($A$2:A2) in the SUM formula.
A formula with regular range references is probably easier to create and read in this scenario. However, the formulas don't always get copied down properly. If you add new rows to the bottom of the table, the running total formula will not create a relative reference to the row the formula is in. Instead, it will likely keep a reference to the last row in the table BEFORE rows were added. This leaves us with incorrect results.
This happens because Excel remembers ONE formula for the entire column and copies it down. When using structured references, the formula text is the SAME in every cell of the running total column. Every cell contains: =SUM(tblSales[[#Headers],[Amount]]:[@Amount]])
Method #2: Mixed References
Another option is to create an absolute reference to the first cell in the column, combined with a structured reference for the last cell.
This method seems to work as well as the referencing the header row. I believe I've had issues with it in older versions of Excel, but can't seem to break it in the current version of Microsoft 365. Therefore, my preference is to use full structured references.
Please leave a comment below if you use this method and/or have issues with it.
Method #3: SUM & INDEX
Another popular alternative uses the INDEX function within SUM.
The INDEX function is used to create a reference to the first cell in the column because we reference a 1 for the row_num argument. INDEX([Amount],1)
This method works just as well, but there are two drawbacks that make me not love it as much:
- The formula is more difficult to read and understand for users of your file that are not familiar with the INDEX function.
- The entire column is referenced in the array argument of INDEX. This creates a colored border around the entire column when in formula edit mode, even though the function evaluates to create a reference to just the first cell. To me, this is a bit more confusing than the reference the to first and current cells that the first method uses.
Which Method is Best?
Honestly, none of these solutions are perfect. Each has their pros & cons. I just find method #1 easier to read, write, and understand.
It would be great if structured references had a better way to reference other cells in the column like the first cell, last cell, cell above, cell below, etc. I know we can create these with nested functions, but it makes it more challenging for other users of our files to understand.
Running Total with Conditions
We can also use these any of these running range references in the SUMIF(S) function to create running totals based on a condition or criteria. In this example I create a running total by region. Here is an example of the formula.
Here is an explanation of each component of the formula:
- The first argument in SUMIF is the range we want to evaluate (tblSales[[#Headers],[Region]]:[@Region]). This uses the same running range reference method.
- The second argument is the criteria or condition that we want to filter the range for ([@Region]). This is the value in the same row the formula is in from the Region column.
- The third argument is the sum range which contains the values we want to sum with the criteria is True (tblSales[[#Headers],[Amount]]:[@Amount]).
Now the running total column reflects running totals for each region. I've highlighted one of the regions (North) in the image below so you can see how the running totals are region-specific.
The running total is especially useful if we are making additions to the list over time and want to track our progression. Using structured references in the formula prevents errors when adding new rows to the table.
Unfortunately, there isn't a straightforward way to create the running range reference with table formulas. The three methods presented in this post should help you create the formulas. I prefer method #1, but it's good to know them all since you will probably come across them in your Excel journey.
The running range reference can also be used with other functions like AVERAGE, AVERAGEIFS, COUNT, COUNTIFS, MIN, MINIFS, MAX, MAXIFS, etc. to calculate different running metrics.
Please let us know if you have any questions, suggestions, or alternative methods in the comments below.
Thanks you for sharing.
Neat trick. Thanks for sharing. Two issues I struggle with
1. place the running total, or total for that matter, above the table. It would still be in the table but it would also be in a cell above, and outside, the table.
2. Create a running balance where the contents of the previous cell is added or subtracted from two cells. BUT not if the previous cell contains character. Thank of a checkbook register. the first row below the column heading ignores the previous row because it has text. I’ve used ifblank but it can get little unwieldly.
Great questions! Here are a few suggestions.
1. You can use the SUM, SUBTOTAL, or AGGREGATE functions to put the total of a column in any cell outside the table. SUBTOTAL and AGGREGATE will only display the total of the visible rows if they are filtered. Here is a recent post on SUBTOTAL, and that formula can be used in any cell outside the table. You will just want to reference the table’s name in the formula.
2. If you want to return a blank for specific rows, then IF(ISBLANK()) is probably the way to go. I’m not sure I fully understand though. It would probably help to see an example.
I hope that helps. Thanks again and have a nice day! 🙂
I was burned one time by =SUM($E$2:$E2) not updating correctly as I added new rows to the table. Ever since I’ve stuck with method 3. Mixed references just seems ugly to me, and referencing it seems inelegant to include the header row in a SUM.
Thanks Jon! Yeah unfortunately there’s no perfect solution here…
Jon & Jon, how about SUBTOTAL ? This formula can serve for both the general running total or a conditioned running total by filtering the table by any of the other fields.
=SUBTOTAL(9,tblSales[[#Headers],[Amount]]:[@Amount]) with including the header
And withouth it:
The second one is my preferred one because its shorter.
And adding a little bit of indenting and some spaces for the formula to breathe, I will leave like this:
INDEX( [Amount], 1 ) : [@Amount]
Thanks for this and your other helpful material.
In the interest of simplicity and thoroughness, couldn’t a 4th method be added? To use Quick Analysis, press Ctrl+q, Totals Tab, scroll to the right, Running Total.
Great suggestion! Unfortunately, that Running Total feature adds a SUBTOTAL formula that uses regular range references ($A$2:A2). It suffers from the same issues mentioned in the article above. It’s definitely an issue that needs to be fixed in Excel.
Thanks, Jon! Very helpful.
Thanks again for the inspiration, Paul! 🙂
Nicely written! I’ve used a mix of the solutions here, generally method 3 is what I use. It’s all situational though. If I *know* the headers will always be there, I don’t mind using them as references in formulas. However, if they may be hidden, those formulas will break and I’ll tend to use entire Table column references instead. And for those who don’t want to use structured references, a simple =SUM(B1,A2) would suffice. As a general rule I’ll never use absolute referencing in Table’s.
Thanks Zack! That’s a great point about Headers being hidden.
Thanks, Jon. FYI: I played around with this because I have a situation where I want to HIDE and exclude rows from the Running Totals, but then go back and unhide them. WOW! I used “=SUBTOTAL(109,tblSales[[#Headers],[Amount]]:[@Amount])”. It seemed to work until I unhid the rows. All the unhidden rows had the same value as the row before the first row I hid. Easily fixed, just recopy the formula down the column. Not sure whether to call it a nuisance or bug.
Great suggestion! That seems like a bug, unless Excel is in Manual Calculation mode. The formula should recalculate when you clear the filter. It does for me.
This is likely the answer to a problem that has long plagued me as I have always used the $A$1:A1 method. I find table nomenclature far less easy to understand as the ranges don’t change.
I’m off to update all of my table running totals formulae. Could take a while though ;-}
Haha, thanks HG! 🙂
How does it need modifying to deal with a running total for eg columns E1 to F10 on a line by line basis?
It auto-reverts to standard formula notation when I try it.
Sorry, I’m not sure I understand your question.
Eg I have a table with data in columns E:F and rows 1:10, row 1 containing the headers and want cumulative totals in column G.
When I highlighted all the headers (E1:F1) and then : and highlighted the first data row (E2:F2) the formulas reverted to standard format and not table nomenclature.
However, I found that if I highlight just the headers row (E1:F1) then : and click on cell F2 it works fine. Sorry if this is unclear too, but writing this from my phone.
thak you Mr. Jon
It’s really useful
So glad I read this post. I figured there had to be a better way than regular refs but never did anything about it – it’s not broken, is it?? Using the structured ref approach exposed some ‘hidden’ errors that had crept in. I’ve added these techniques to my Snippets docx. Keep up the good work.
Please help!! out of ideas and places to look.
I have a table with a list of people in one column, their emails in another column and a column with travel days. I want to be able to look for the total number of travel days of one person in that table. Im currently using index and match, this works only if the person im looking for has one entry. If person ‘a’ has more than one entry with different travel days, the formula is only returning the first value it finds for person ‘a’, i would need the total of days. What is the best way of doing this without using a macro.
Name Email Travel Days
A [email protected] 2
B [email protected] 3
C [email protected] 5
A [email protected] 52
A [email protected] 5
B [email protected] 58
D [email protected] 30
C [email protected] 75
My current formula looks for the email of the person which is the unique value for each member, and matches their travel days, only returning the first value found. I need to be able to get the sum of all the travel days associated with that email.
This is amazing, thank you for sharing
Thank you for this simple explanation.
I was wondering if you could help me. I understand the gist of what you are saying, but am having a difficult time applying it to my situation. So I apologize if the answer is already here and I’m just not getting it!
Column A contains a Part No.
Column C contains Inventory On Hand.
Column B contains qty needed for the order.
I want to subtract the qty needed from what is On Hand. BUT if the next row contains the same Part number as in the previous row I want to deduct the new row’s qty needed from the total of the previous cell.
Part Qty OH
421 200 800 (qty – OH qty)
421 150 450 (qty – OH qty AFTER the
first Qty is deducted)
Now the harder part is that lets say the third row is a New PN. I now want the formula to look only at what is OH for the New PN minus Qty OH. Does that make sense?
Part Qty OH
421 200 800 (qty – OH qty)
421 150 450 (qty – OH qty AFTER the
first Qty is deducted)
2345 300 350 Needs to reset itself
6789 200 400
I need to know if we have enough parts in stock to supply all demand or whether we need a new job to produce more parts.
I hope this makes sense.
I would appreciate any feedback you might be able to give me.
I used =AGGREGATE(9,3,tblSales[[#Headers],[Amount]]:[@Amount]) inside the table; and it respects any filtering of other columns! Great for showing cumulative percentages of only the visible values.