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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!