2

I'm using an Excel table to sum some values. On a certain column, I want a running total (that is, a sum of all values previous and up to the current row). Doing that on ranges is easy, all I need to do is use an absolute cell address for the start of the range (using the $ notation) and use a relative address for the current row.

However, using tables, things get messy - the notation for ranges in tables use the [[]] syntax. Is there a way to denote a range beginning at the top of the column until the current row?

Bruno Brant
  • 8,226
  • 7
  • 45
  • 90

3 Answers3

2

Use the Header as your starting point:

=SUM(Table1[[#Headers],[Num]]:[@Num])

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Oh, wow. Game changer. I never realised you could do this with table references. I've been using the sheet references whenever I have needed to do this in a table. I.e. $A$1:$A2 – Gravitate Jun 06 '22 at 15:29
  • I don't quite follow. Is that formula meant to be typed in literally as `=SUM(Table1[[#Headers],[Num]]:[@Num])`? I'm getting an error about it not being a valid formula, even if I define a table. – Mark Meuer Feb 28 '23 at 21:34
  • @MarkMeuer This is not how this forum works. If you have a problem implementing this answer then ask a new question and refer back. That way you can show how you implemented it. This works, as you can see I put that formula in as typed and it did what it was supposed to. There are too many possible reasons as to why it does not work for your specific use for me to guess without seeing how you set things up. So create a new post. – Scott Craner Feb 28 '23 at 21:43
  • @ScottCraner Actually, asking clarifying questions in comments is exactly how the forum works. You could have just answered something like, "Yes, I enter that value exactly as shown." – Mark Meuer Feb 28 '23 at 21:55
  • @MarkMeuer https://meta.stackoverflow.com/questions/363625/would-it-be-wrong-asking-questions-to-an-answer-er – Scott Craner Feb 28 '23 at 22:02
1

I suggest you go with @ScottCraner but another option is:

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

enter image description here

This can also be adapted to allow you to sum to any row, not just the current one. e.g. to sum the first 3 rows:

=SUM(INDEX([Num],1):INDEX([Num],3))
Gravitate
  • 2,885
  • 2
  • 21
  • 37
0

You want a separate column with the totals up to this row? That is the previous total plus the current value.

Or, assuming column A has the values and B has the running total :

  • cell B1 =A1
  • cell B2 =B1+A2
  • and copy that formula down as far as you need
Hans Kesting
  • 38,117
  • 9
  • 79
  • 111