Cannot set formula to column formula due to absolute reference
=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

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

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

I want the formula to only look at rows above.

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.


Happy to help!😁 @Dantastic

Help Article Resources
Categories
Check out the Formula Handbook template!