How to create running total using Excel table stru

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

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

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.

Answer 3

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

Answer 4

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

Answer 5

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 6

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