This article shows how to calculate running totals in Excel Tables by using structured references. If your data is not in a Table, please continue with this article: How to calculate running count
Syntax
=SUM(INDEX([column of values],1):[@ column of values])
Steps
- Start with SUM =SUM(
- Continue with INDEX that becomes a reference point for start cell INDEX(
- Select the whole column that is summed [Amount],
- Type 1 as the row index argument of the INDEX function
- Close the INDEX function and put a colon to continue to end the range reference ):
- Select the first cell in the column of values [@Amount]
- Close the SUM function and finish the formula with )
How
To calculate running totals in Excel Tables is different than regular ranges. While it is enough to lock the first cell of range reference in SUM function (e.g. $C$3:C8), those mixed references are not populated correctly by a Table. To make it work similarly, first lock the reference using INDEX function with argument 1.
Another important point is, because we are using a Table, we can use structured references which is a special feature of Tables. The structured references are easier to read than regular references and you do not need to worry about updating ranges as your table grows. Excel generates the structured references as you click or select a cell in table while you are writing a formula.
Tip: While column names in brackets represents the columns, column names with @ sign indicates the cell in the same row. So;
- [Amount] : column named "Amount"
- [@Amount] : cell in the same row with formula under "Amount" column
We can now set our first reference for the SUM function like below, by pointing the first cell in the column "Amount".
INDEX([Amount],1)
The next step is to point the last reference of the range for the SUM function. As we just mentioned, @ sign indicates the cell in the same row. So it is easy to guess that we need to put [@Amount] as ending reference for the SUM function.
=SUM(INDEX([Amount],1):[@Amount])
