19

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.

studgeek
  • 14,272
  • 6
  • 84
  • 96

8 Answers8

34

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

studgeek
  • 14,272
  • 6
  • 84
  • 96
  • IMHO, that's one of the best way to deal with your issue. Don't forget you can accept your own answer :) – JMax Aug 17 '11 at 20:28
  • 12
    You can also use this: =SUM(INDEX([WTaskUnits],1):[@WTaskUnits]) – Ivan Neeson Jan 10 '16 at 23:42
  • Will this be optimized under the hood? Or will it really redundantly sum the initial segments over and over again? – Alan Jul 21 '16 at 16:59
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.

James Bayley
  • 233
  • 2
  • 7
2

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.

チーズパン
  • 2,752
  • 8
  • 42
  • 63
Michigan Al
  • 21
  • 2
2

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

sstiebinger
  • 43
  • 4
1

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

Darush
  • 11,403
  • 9
  • 62
  • 60
1

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.

Sleette
  • 366
  • 3
  • 13
  • Thanks, its nice to get confirmation. It a little surprising they didn't add a little more row support with structured references. I guess they felt using the existing formulas like INDEX and OFFSET around it was enough. – studgeek Aug 22 '11 at 12:40
0

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

AndréB
  • 65
  • 7
  • It looks like your using a Pivot Table, where the OP is looking for a solution for a regular Excel Table (i.e. not a Pivot Table). – dav Aug 29 '16 at 18:41
-1

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

Greg Starr
  • 1