Looking to create a formula that automatically counts X of X and returns appropriate values.s
I currently have a formula that counts how many matching cells in a column there are. The cells are a combination of numbers and letters. For example: CA0059 or CD0101. My current formula only counts the matching number, so for example is CA0059 shows up in 4 different cells it will count it 4 times.
My current Formula : =COUNTIF([Full Sample Number]:[Full Sample Number], [Full Sample Number]@row)
What I want to do is in a different column count x of x, automatically, so essentially automatically notice that CA0059 shows up 4 times and then have 4 rows with 4 different values such as:
Row 1: 1 of 4
Row 2: 2 of 4
Row 3: 3 of 4
Row 4: 4 of 4
I'm assuming it'd be: a separate formula + "of" + my current formula, but I'm unsure what the separate formula would be.
Answers

@C. Ruiz You need an auto number column as a helper. Then you can use the following:
=COUNTIFS([Full Sample Number]:[Full Sample Number], [Full Sample Number]@row, [AutoNum]:[AutoNum], <=[AutoNum]@row)+ " of "+COUNTIF([Full Sample Number]:[Full Sample Number], [Full Sample Number]@row)

Thank you it worked perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!