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.

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

--
HTH

Bob Phillips

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

"Michael" wrote in message
...
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.

"Michael" wrote:
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.

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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)


"Michael" wrote in message
...
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.