Cannot set formula to column formula due to absolute reference

Dantastic

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

jmyzk_cloudsmart_jp

@Dantastic

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.

https://app.smartsheet.com/b/publish?EQBCT=3473ef2965944090bf862cb4aea36671

John Shane

Are you wanting this formula to only look at rows above or at all rows of your sheet? If all rows, then

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

should work for you as a column formula.

Cheers,

John

Dantastic

https://community.smartsheet.com/discussion/comment/410861#Comment_410861

I want the formula to only look at rows above.

jmyzk_cloudsmart_jp

@Dantastic

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.

https://app.smartsheet.com/b/publish?EQBCT=3473ef2965944090bf862cb4aea36671

Dantastic

@jmyzk_cloudsmart_jp

Thank you!

I will give this a shot!

jmyzk_cloudsmart_jp

Happy to help!😁 @Dantastic

Dantastic

@jmyzk_cloudsmart_jp

Everything worked.

Thank you for the help!