How to create running total using Excel table structured references?

Question

I'm looking for a way to create a running total (total of the current row and above) using Excel table structured references.

I know how to do it using the old row/column based way:
=SUM($A$2:$A2)

And I know how to total an entire column using structured references:
=SUM([WTaskUnits])

And I know how to get the current cell using [#ThisRow], but I'm not sure how to get the first row of the table to use it in a SUM.

Answer 1

Actually, I did just figure out one way of doing it using INDEX, but feel like there should be a more structured reference way. Still, for others looking to do this here is what I came up with:
=SUM(INDEX([WTaskUnits],1):[[#This Row],[WTaskUnits]])

I use INDEX to get the first cell of the column (equivalent of $A$2 in my row/column example) and use [#This Row] normally to get the cell in current row (equivalent of A2 in my row/column example).

Answer 2

=SUM(OFFSET([WTaskUnits],0,0,ROW()-ROW([[#Headers],[Running Total]])))

The table has two columns [WTaskUnits] and [Running Total]. The formula above sums the range of cells as requested.

The first arguments of the OFFSET function define the starting point of the sum. The fourth term,

ROW()-ROW([[#Headers],[Running Total]]

is a useful idiom for the number of the current row in the table.

Answer 3

I realize this is an old thread, but I finally have a solution I would like to offer.

=IF(ISNUMBER(OFFSET([@Balance],-1,0)),OFFSET([@Balance],-1,0)+[@Amount],[@Amount])

In the instance of the first data row, the offset points to the header, which is not a number, therefore the result is only the Amount column.

The remaining rows give you the previous Balance from the OFFSET plus the current row Amount.