Looking to create a formula that automatically counts X of X and returns appropriate values.s

C. Ruiz

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.

sharkasits

@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)

C. Ruiz

https://community.smartsheet.com/discussion/comment/356714#Comment_356714

Thank you it worked perfectly!