Unique Integer Value Per Entry
I am trying to write a column formula that will assign an integer value to a row based on the number of times a unique value appears in a row.
Answers

does this work?
=COUNTIF([Serial Number]:[Serial Number], [Serial Number]@row)
Sincerely,
Jacob Stey

Hi Jacob,
This is a solution I have tried before.
I need the integer value in Instance and Cycle to match.
The function provided does not produce the desired result.
I basically need a formula that adds 1 to a count that will not alter previous entries.
Essentially Cycle should return an integer value for each separate occurrence of a serial number.
For example, the first time a serial number appears the cycle would equal 1 and the second time a serial number appears in a column the cycle would equal 2 but the cycle integer for the first occurrence of the serial number would remain as 1.
I am not sure if my description makes sense.
I have been trying to figure this out for awhile but am having a difficult time finding anything online.

I figured it out!
=COUNTIF([Serial Number]$1:[Serial Number]@row, [Serial Number]@row)
Thanks for the help!
I limited the range to count only from the first row to the current row.

There is a cheat way, where you setup the formula I showed you above. Once you do this, you can setup automation to copy the row to a new sheet called "Countif Saver" or something like that. Have an auto number, or some kind of identifier column to INDEX/MATCH and pull cycle number back into the sheet.
This will save the number in a text box at the time it is created.
Does this make sense?
Sincerely,
Jacob Stey

awesome solution! glad you figured it out!
Sincerely,
Jacob Stey

I got the result I want but since I used [Serial Number]$1 I can't set as a column formula.
If I remove $1 and leave formula as =COUNTIF([Serial Number]:[Serial Number]@row, [Serial Number]@row) the formula is unparseable.
Maybe I can write the formula on another sheet and reference it where I need it and game the system that way.

Let me know what you figure out. If all else fails, you can try the cheat way referenced above
Sincerely,
Jacob Stey
Help Article Resources
Categories
Check out the Formula Handbook template!