Cannot set formula to column formula due to absolute reference

Dantastic ✭✭
edited 02/15/24 in Formulas and Functions

=COUNTIF([Serial Number]$1:[Serial Number]@row, [Serial Number]@row)

I need to convert the formula above into a form that allows me to set it as a column formula.

=COUNTIF([Serial Number]:[Serial Number]@row, [Serial Number]@row) does not work.

Is there a work around for COUNTIF?

I was thinking I could write a similar formula with IF statements but can't figure it out.


Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/23/23 Answer ✓


    If you can use helper columns, you can convert the formula with absolute reference to a column formula.

    =COUNTIFS([Serial Number]:[Serial Number], [Serial Number]@row, [Row No]:[Row No], <=[Row No]@row)

    "[Row No]:[Row No], <=[Row No]@row" makes the COUNTIF count "only looking at rows above".

    =MATCH([Row ID]@row, [Row ID]:[Row ID])

    Usually, the auto number, Row ID, should be enough, so [Row ID]:[Row ID],<=[Row ID]@row should work unless you change the positions of the rows. If you anticipate row position change, Row No made by the MATCH function ensures the function works regardless of the row position after the row creation.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!