Referencing a column from the top down to the current row - WITHOUT ABSOLUTE REFRENCE

David Dolch

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

Paul Newcome

You would insert two more columns.

System Generated Auto-number column with no special formatting.

Text/number column with the following column formula:

=MATCH([Auto-number]@row, [Auto-number]:[Auto-number], 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)

Paul Newcome

You would insert two more columns.

System Generated Auto-number column with no special formatting.

Text/number column with the following column formula:

=MATCH([Auto-number]@row, [Auto-number]:[Auto-number], 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)

David Dolch

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

Paul Newcome

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.