Formula to count duplicates

  • Thread starter Thread starter Jane
  • Start date Start date
J

Jane

I have a list of names in a column. Some names are duplicated. I want to
keep the duplicated names but I also want to count how many times the names
are duplicated. Can anyone help please?
thanks in anticipation
 
Assuming your data in column A1:A100

In B1: =IF(COUNTIF($A$1:$A$100,A1)>1,COUNTIF($A$1:$A$100,A1),"")
copy down
 
It works but it gives a 1 for the first duplicate entry and then the next
entry has the total number of entries. So if Mary has 4 entries it shows
Mary 1 Mary 4. How do I get rid of the first number 1 please? No worries if
it can't be done, I'm just grateful you solved it anyway! Thanks
 
Sooooo sorry, Glenn. You are quite right, I copied and pasted again and it
works beautifully. You're a sheer genius! Thanks again.
 
Or, if you only want the count to show up the first time the name appears on the
list:

=IF(COUNTIF($A$1:A1,A1)>1,"",COUNTIF(A1:$A$100,A1))
 
If entered properly, it should not do that. Exactly where is your data and
exactly what formula did you enter and copy down?
 
Back
Top