running sum without explicit cell reference
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
Best Answer

To add to @Mark Cronk's idea.
Add the below 2 helper columns:
 "LINEID" : Auto Number Column
 "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 0)
Then your formula would be this:
=IF(CurrentRecordHelper@row = 0, "", SUMIFS(CurrentRecordHelper:CurrentRecordHelper), [ROW#]:[ROW#], <= [Row#]@row))
The downside of using just the AutoNumber 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.
Answers

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 AutoNumber. 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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

To add to @Mark Cronk's idea.
Add the below 2 helper columns:
 "LINEID" : Auto Number Column
 "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 0)
Then your formula would be this:
=IF(CurrentRecordHelper@row = 0, "", SUMIFS(CurrentRecordHelper:CurrentRecordHelper), [ROW#]:[ROW#], <= [Row#]@row))
The downside of using just the AutoNumber 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.

Many thanks i will try that right now

many thanks for your help. works perfectly and it's been bugging me for ages
Help Article Resources
Categories
Check out the Formula Handbook template!