How to create running total using Excel table structured references?

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 struc开发者_Go百科tured 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.

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).

=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.

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.

Note, if you use =SUBTOTAL(109,...) instead of =SUM(...) then your formula will respect filters on the table.

Not sure what you mean by more structured, the way you're doing it is fine. You could also simply start your range in the cell below the table header:

OFFSET([[#Headers],[WTaskUnits]],1,0,1,1):

But I wouldn't call that more structured, simply different. There's no table reference for a partial range of rows in a columns, so my suggestion would be to stick with what you've got.

Use the following formula:

   =SUM( INDEX([Values],1) : [@Values] )

We use INDEX to return the first cell in the Values column, and simply use the Structured Reference to the current row to return the second cell in the range we want to sum.

Source

I created my running total using Excel's Pivot:

• Sum values -> Sum of ...
• Show values as -> Running total

My 2 cents, 5 years late (and maybe missing the point).

For a fixed table of data 1. goto the last cell in the column you want the subtotal in 2. subtotal the column that has the data in it and absolute reference the first row 3.copy the formula up to the first row