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

04/20/21
Accepted

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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)

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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)

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

Sign In or Register to comment.