Hey Bob,

I think the OP needs to only count unique values where all of each unique
value counted in column P has a value in column F. Therefore, since one
"def" does not contain a value in column F, then the other one can't be
included when counting the unique values. I'm not sure how that'll be
fixed, but I'm sure you or someone here will.

--

"Bob Phillips" wrote in message
...
=SUMPRODUCT(INDEX((F1:F8<"")*(A1:A8="001")*(MATCH ($P$1:$P$8,$P$1:$P$8,0)=ROW($P$1:$P$8)-ROW($P$1)+1),0))

I get 3

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Joe Gieder" wrote in message
...
First thank you for looking at this post and helping to find a solution
What I'm tying to do is create a formula that will count how many unique
values are in column P if column F is not blank and column A matches a
value
in another worksheet. I can't use the auto filter because the formula
will be
used within another formula (I hope).

A F P
001 1 abc
001 2 abc
001 5 abc
001 2 def
001 def
001 1 ghi
002 10 abc
003 20 abc
The result I'm looking for is 2 because I'm using 001 as the criteria to
match and supplier def has a blank in column F even though there's a
value
with the other def.

TIA
Joe