# 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

Andy

Tags:

• 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.

• 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

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:

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.

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!