How to create running total using Excel table stru

问题:

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.

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

回答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.

回答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.

回答4:

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

回答5:

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.

回答6:

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

回答7:

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

回答8:

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