I'd opt for the dynamic named range.

I "discovered" this setting up a template. I needed to extract uniques from
a range and couldn't figure out why it didn't work. I knew the formula was
correct. As soon as I used the dynamic range it worked as expected.

I think I'll put this formula in my stash.

It's now in mine!

Good job!

Biff

"Ron Coderre" wrote in message
...
Biff

I ran your scenario and (of course) ran into the DIV/0 error.

I only see 2 ways around it (neither very attractive):

1)Dynamic Range Names
Example:
Name: rngListOne
Refers to:
=OFFSET(Sheet8!$A$1,0,0,MAX(INDEX((Sheet8!$A$1:$A$ 20<"")*ROW(Sheet8!$A$1:$A$20),0)),1)

2)This hideous thing:
=SUMPRODUCT((COUNTIF(A1:INDEX(A1:A20,MAX(INDEX((A1 :A20<"")*ROW(A1:A20),0))),B1:INDEX(B1:B20,MAX(IND EX((B1:B20<"")*ROW(B1:B20),0)))&"")0)/COUNTIF(B1:INDEX(B1:B20,MAX(INDEX((B1:B20<"")*ROW (B1:B20),0))),B1:INDEX(B1:B20,MAX(INDEX((B1:B20<" ")*ROW(B1:B20),0)))&"")*(B1:INDEX(B1:B20,MAX(INDEX ((B1:B20<"")*ROW(B1:B20),0)))<""))

The best pre-emptive solution is to just make sure the used range includes
the referenced range.

Thanks for taking the time to help me work through this one.
I think I'll put this formula in my stash.

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

XL2002, WinXP


"T. Valko" wrote:

Ok, I figured out what the problem was. See these screencaps:

http://img153.imageshack.us/img153/9...sdiverr2od.jpg

http://img186.imageshack.us/img186/5...esnoerr4zf.jpg

You'll notice in the first screencap the result of the formula is a
#DIV/0!
error and in the 2nd screencap the result is correct.

Notice the range in the formula used in the first screencap and the range
used in the formula in the 2nd screencap. Although I don't know this to
be
fact, I think this type of formula has a problem with the used range. I
mentioned this in a thread several months ago and Bob Phillips verified
that
he also experienced this before.

In the first screencap the formula references exceed the current used
range.
In the 2nd screencap I changed the references to be within the current
used
range.

Try this test. I'm using Excel 2002 on WinXP (all updates current)

Open a *new fresh empty* workbook.
Enter this formula in B1:

=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))

The result is 0 as you'd expect since the referenced range is empty.
Now, start entering data in A1 and on down one cell at a time.
The formula returns #DIV/0! until an entry is made in A10. This also sets
the used range referenced in the formula.
Now, if you clear the contents of the range and then start entering new
data
the formula works as expected.

Another one of Excel's "features"!

So, your formula does in fact work and I like it!!!!!!!!!

Biff

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