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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!