How to create running total using Excel table structured references?

excel excel-2007 excel-formula
studgeek picture studgeek · Aug 17, 2011 · Viewed 17.9k times · Source

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

studgeek picture studgeek · Aug 17, 2011

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

Related questions

Excel formula to reference 'CELL TO THE LEFT'

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More