Hey, Biff....I'm a bit puzzled by your post.

This formula
=SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<""))

.....returns 2 for this one, which is correct (10 and 22 are hits)
.............A..........B
1........10..........0
2........22.........10
3........10.........10
4........17.........30
5........10.........22
6.....................22
7........22..........0

....and returns 3 for this one, also correct (10, 22 and 0 are hits)
.............A..........B
1........10..........0
2........22.........10
3........10.........10
4........17.........30
5........10.........22
6.........0..........22
7........22..........0

Are you saying it works?
or am I missing something?

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Try it on this data set:

............A..........B
1........10..........0
2........22.........10
3........10.........10
4........17.........30
5........10.........22
6.....................22
7........22..........0

Or, this one:

............A..........B
1........10..........0
2........22.........10
3........10.........10
4........17.........30
5........10.........22
6.........0..........22
7........22..........0

Biff

"Ron Coderre" wrote in message
...
Thanks, Biff....
I ran into the "formula blanks" problem a little while ago and adjusted
the
formula....but wasn't sure it would be an issue. As you pointed out..it
is.

This one is the latest in a series of final formulas : \

=SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<""))

Note: The only potential issue I can see with that one is that it wont
match
a formula blank in Col_A against a formula blank in Col_B. Although,
that's
probably a bonafide feature (since formula blanks are intended to be
blanks,
right).

Of course, error values are an issue in general.

Your thoughts?

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Try it on this data set:

............A..........B
1........10.............
2........22.........10
3........10.........10
4........17.........30
5........10.........22
6.........0..........22
7........22.............

Then put another 0 in B7. Also seems to get tripped up on formula blanks.

Biff

"Ron Coderre" wrote in message
...
OK....pretty sure I got it this time:

For 2 lists (A1:A100 and B1:B100)
This formula returns the count of unique items from Col_A that are in
Col_B

=SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&""))

It is durable against text, numbers, and blanks.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Well THAT sure didn't work as soon as I put some duplicates in.

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

=SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"adodson" wrote:

I'm looking to compare two lists (a1:a10 compared to b1:b10,
unnamed). Then,
return the count of original matches between the two lists. For
example, if
Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count
it
only once.

Any advice greatly appreciated? Thank in advance.

P.S. = this will be incorporated into an If statement.