running sum without explicit cell reference

Andy Counsell

Hi

i have a formula that sums values in a column from row 1 to current row

=IF(CurrentRecordHelper@row = 0, "", SUM(CurrentRecordHelper$1:CurrentRecordHelper@row))

is there any way to replicate this without the explicit row reference ($1), so it can be converted to a column formula

many thanks in advance

Andy

Leibel S

@Andy Counsell

To add to @Mark Cronk's idea.

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

Then your formula would be this:

=IF(CurrentRecordHelper@row = 0, "", SUMIFS(CurrentRecordHelper:CurrentRecordHelper), [ROW#]:[ROW#], <= [Row#]@row))

The downside of using just the Auto-Number is if someone moves the rows around it may add that to your sum (even though it is lower down on the sheet). By creating the helper ROW# column it will always only pull from the values above it.

Mark Cronk

Hi @Andy Counsell ,

I think you have to use a workaround if you want to use a column formula. Add a Row ID column as an Auto-Number. The column will populate with numbers low to high, top to bottom.

In your sum column use the formula:

=IF(CurrentRecordHelper@row = 0, "", SUMIFS(CurrentRecordHelper:CurrentRecordHelper), [Row ID]:[Row ID], <= [Row ID]@row))

Work?

Mark

Leibel S

@Andy Counsell

To add to @Mark Cronk's idea.

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

Then your formula would be this:

=IF(CurrentRecordHelper@row = 0, "", SUMIFS(CurrentRecordHelper:CurrentRecordHelper), [ROW#]:[ROW#], <= [Row#]@row))

The downside of using just the Auto-Number is if someone moves the rows around it may add that to your sum (even though it is lower down on the sheet). By creating the helper ROW# column it will always only pull from the values above it.

Andy Counsell

Many thanks i will try that right now

Andy Counsell

many thanks for your help. works perfectly and it's been bugging me for ages