Formula to count duplicates

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

T

Teethless mama

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

J

Jane

Many thanks it worked.

Teethless mama said:
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

J

Jane

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

J

Jane

Sooooo sorry, Glenn. You are quite right, I copied and pasted again and it
works beautifully. You're a sheer genius! Thanks again.

G

Glenn

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

G

Glenn

If entered properly, it should not do that. Exactly where is your data and
exactly what formula did you enter and copy down?