Counting a Unique identifier in a column

Omotola Ashafa

Hello SS Community,

I am halfway there but seem to be doing something wrong- I want to count how many times a Person shows up in the list and record the number.

I have this formula =IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 1, 1) and it is working perfectly to record when the person number in the Person number column shows up more than once but when I add the second layer to record two when the person number shows more than two =IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 1, 1) IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 2, 2)) It does not do anything and still records 1 even when the exact person number is in the column twice.

I also tried to use this formula =COUNTIF([Person Number]:[Person Number], [Person Number]@row) which worked but the reason I can't use this is I don't want to count the first occurrence of the person number in the sheet- I am trying to use it to count how many times the person number was rescheduled, so the first occurrence should be removed from the count.

Heather Duff

@Omotola Ashafa ,

Try this, then:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row)-1,"")

Really all you're doing is subtracting 1 from the count if it qualifies, but it should do the trick!

Heather Duff

Hi @Omotola Ashafa,

Try this:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row),"")

This should only list the number of occurrences if there's more than one row with that person; otherwise, it will be blank.

Let me know if it works!

Best,

Heather

Omotola Ashafa

Thank You @Heather D ,

It works as you explained. The only thing is we would like the total rescheduled number only, so we want the first occurrence removed from the count.

Heather Duff

@Omotola Ashafa ,

Try this, then:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row)-1,"")

Really all you're doing is subtracting 1 from the count if it qualifies, but it should do the trick!

Omotola Ashafa

@Heather D

It worked, thank you so much and thanks for explaining the formula too

Heather Duff

@Omotola Ashafa You're welcome! I'm glad it worked.