Explanation
At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where “first” is the first cell in the Total column, and “current” is a reference to a cell in the current row of the Total column.
To get the a reference to the first cell, we use INDEX like this:
INDEX([Total],1)
Here, the array is the entire “Total” column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value.
To get a reference to the current row, we use:
[@Total]
This is the standard structured reference syntax for “this row”.
As the formula is copied down the column, the reference to the first cell doesn’t change, but the referent to the current cell changes at each row. The result is a reference that expands. The SUM function sums the values in the range at each row, creating a running total.
Simple expanding range
Why not use a simple expanding range like this?
=SUM($E$5:E5)
For some reason, this kind of mixed reference becomes corrupted in an Excel Table as rows are added. Using INDEX with a structured reference solves the problem.
Explanation
This formula uses structured references to refer to the “Amount” column in each table. The structured references in this formula resolve to normal references like this:
=SUM(Table1[Amount],Table2[Amount])
=SUM(C7:C11,F7:F13)
=1495.5
When rows or columns are added or removed from either table, the formula will continue to return correct results. In addition, the formula will work even if the tables are located on different sheets in a workbook.
Alternative syntax with Total row
It is also possible to reference the total row in a table directly, as long as tables have the Total Row enabled. The syntax looks like this:
Table1[[#Totals],[Amount]]
Translated: “The value for Amount in the Total row of Table1”.
Using this syntax, the original formula above could be re-written like this:
=SUM(Table1[[#Totals],[Amount]],Table2[[#Totals],[Amount]])
As above, this formula will work even when the table is moved or resized.
Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error.
