counting names

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[brianjohnalford]

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 -

Many thanks to both of you. both worked. Cheers