counting names

  • Thread starter Thread starter brianjohnalford
  • Start date Start date
B

brianjohnalford

I have a two columns A with surname and B with first name, but entered
multiple times:

eg:

Smith, John
Smith, John
Smith, Arthur,
Rabbit, Peter,
Rabbit, Peter
Rabbit, Peter

What's the easiest way to total up the number of people there are? In
this case 3.

Thanks
 
Try this:-

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""),IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

It;s an array so Ctrl+Shift+enter
 
I missed they are in 2 columns so concatenate them into a single column first

in C1 =A1&B1

Apply the formula to the concatenated column.

Mike
 
one way:

InC1: =A1&B1 and copy down

in D1: =SUM(1/COUNTIF($C$1:$C$6,$C$1:$C$6))

Enter with Ctrl+Shift+Enter

HTH
 
one way:

InC1: =A1&B1 and copy down

in D1: =SUM(1/COUNTIF($C$1:$C$6,$C$1:$C$6))

Enter with Ctrl+Shift+Enter

HTH







- Show quoted text -
Click to expand...

Many thanks to both of you. both worked. Cheers
 
Back
Top