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.