Counting cells

M

Michael

Hi, I have a list of names in column 1 and another list of names in column 2.
I would like to count how many of the names in column 2 are in column 1. I
have a feeling it will be countif but because the criteria is a range I can't
get it to work.
Any help would be appreciated.
Thanks.
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A10<>""),--(ISNUMBER(MATCH(A1:A10,B1:B10,0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
M

Max

Michael said:
Hi, I have a list of names in column 1 and another list of names in column 2.
I would like to count how many of the names in column 2 are in column 1. I
have a feeling it will be countif but because the criteria is a range I can't
get it to work.
Click to expand...

One way ..

Assuming list in col A is within A1:A100, in col B within B1:B20 (say)
Try in C1: =SUMPRODUCT(--ISNUMBER(MATCH(B1:B20,A1:A100,0)))
 
L

Larry S

I will assume Columns as follows:

A - list of names you want to search and count how many times a name
appears.

B - list of names you want to look up in col. A.

C - empty column where we can place the count formula

assume you want to look up the name found in B3, then in C3 place the
formula:

=COUNTIF(A:A,B3)
 
Top