Referencing a column from the top down to the current row  WITHOUT ABSOLUTE REFRENCE
I am trying to refer to a column from the #1 row down to the current row in a formula
For example say I have a column called Monthly Cost, I could have a formula
=SUM( [Monthly Cost]$1:[Monthly Cost]@row)
And that formula will very nicely give me a running subtotal line by line
Except for one issue  I want to make it a column formula  and Smartsheet does not like absolute references in column formulas
Smartsheet does not like
=SUM( [Monthly Cost]:[Monthly Cost]@row)
that is apparently a syntax error.
Any ideas on how to reference a column from row 1 to the current row without using an absolute cell refrence ?
Thanks
Best Answer

You would insert two more columns.
System Generated Autonumber column with no special formatting.
Text/number column with the following column formula:
=MATCH([Autonumber]@row, [Autonumber]:[Autonumber], 0)
Then you would change your SUM to a SUMIFS and say to sum the Monthly Cost for all rows where the text/number is less than or equal to the text/number @row.
=SUMIFS([Monthly Cost]:[Monthly Cost], [Text/number]:[Text/number], @cell <= [Text/number]@row)
Answers

You would insert two more columns.
System Generated Autonumber column with no special formatting.
Text/number column with the following column formula:
=MATCH([Autonumber]@row, [Autonumber]:[Autonumber], 0)
Then you would change your SUM to a SUMIFS and say to sum the Monthly Cost for all rows where the text/number is less than or equal to the text/number @row.
=SUMIFS([Monthly Cost]:[Monthly Cost], [Text/number]:[Text/number], @cell <= [Text/number]@row)

That should work. Thank You.
I simplified the above example to just concentrate on the issue.
In reality, I am not using a simple SUM, I am using two COUNTIF statements nested within an other IF statement, but the concept should work. It is just going to be a long formula.
Thanks Again

Happy to help. 👍️
The COUNTIF statements should only need to change to COUNTIFS and then add a single range/criteria set to both to account for the text/number (row#) column.
Help Article Resources
Categories
Check out the Formula Handbook template!