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 cant 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 Im looking for is 2 because Im 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

=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

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

Bob,
Thank you for the formula and the help. I was also getting 3 but the answer
should be 2 because I think it's counting def. It shouldn't count def because
one of them is blank in column F and if it's blank it should ignore it. Can
this be done?

Thank you for your help
Joe

"Bob Phillips" wrote:

=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

Joe Gieder wrote...
Thank you for the formula and the help. I was also getting 3 but
the answer should be 2 because I think it's counting def. It
shouldn't count def because one of them is blank in column F and
if it's blank it should ignore it. Can this be done?
....
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).

As long as that other formula isn't too complex.

_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.

If your table above were in A2:P9, try the array formula

=COUNT(1/FREQUENCY(IF((A2:A9="001")*(COUNTIF(P2:P9,P2:P9)
=MMULT(--(P2:P9=TRANSPOSE(P2:P9)),--(F2:F9<""))),
MATCH(P2:P9,P2:P9,0)),ROW(P2:P9)-MIN(ROW(P2:P9))))

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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 cant 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 Im looking for is 2 because Im 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

I don't understand how it could be 3 when I don't want it to count the def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi. Only
abc and ghi should be counted though since they all have a value in column F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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 cant 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 Im looking for is 2 because Im 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

It would still be 3, the first occurrence of def has a value in F


--
Regards,

Peo Sjoblom



"Joe Gieder" wrote in message
...
I don't understand how it could be 3 when I don't want it to count the def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi.
Only
abc and ghi should be counted though since they all have a value in column
F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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

I think the OP wants to count a unique value only if ALL the occurrences of
that value match the criteria. So if one accurrence of "def" does not match
that given criteria, then none of the occurrences of "def" will be counted.
I think this is going to have to be done with VBA.

--

"Peo Sjoblom" wrote in message
...
It would still be 3, the first occurrence of def has a value in F


--
Regards,

Peo Sjoblom



"Joe Gieder" wrote in message
...
I don't understand how it could be 3 when I don't want it to count the def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi.
Only
abc and ghi should be counted though since they all have a value in
column F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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

Then abc shouldn't be counted either since it has 2 values that does not
match the criteria in the first column Which means that the OP has a
different criteria standard



--
Regards,

Peo Sjoblom





"PCLIVE" wrote in message
...
I think the OP wants to count a unique value only if ALL the occurrences of
that value match the criteria. So if one accurrence of "def" does not
match that given criteria, then none of the occurrences of "def" will be
counted. I think this is going to have to be done with VBA.

--

"Peo Sjoblom" wrote in message
...
It would still be 3, the first occurrence of def has a value in F


--
Regards,

Peo Sjoblom



"Joe Gieder" wrote in message
...
I don't understand how it could be 3 when I don't want it to count the
def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi.
Only
abc and ghi should be counted though since they all have a value in
column F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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

I think everyone is missing what the OP is asking. He is saying that if
**any** column F cell is blank, the **all** the cells in column P having the
same text as that which corresponds to the blank column P row should **not**
be counted... none of them. Since the cell in row 5 of column F is blank,
and since row 5 column P has "def" in it, then no column P cell with "def"
in it should be counted.

Rick


"Peo Sjoblom" wrote in message
...
It would still be 3, the first occurrence of def has a value in F


--
Regards,

Peo Sjoblom



"Joe Gieder" wrote in message
...
I don't understand how it could be 3 when I don't want it to count the def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi.
Only
abc and ghi should be counted though since they all have a value in
column F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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

But OP wants the count to be 2 so he must have some kinda rule that is not clear
to the responders.............or himself<g


Gord

On Tue, 7 Aug 2007 16:03:50 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I think everyone is missing what the OP is asking. He is saying that if
**any** column F cell is blank, the **all** the cells in column P having the
same text as that which corresponds to the blank column P row should **not**
be counted... none of them. Since the cell in row 5 of column F is blank,
and since row 5 column P has "def" in it, then no column P cell with "def"
in it should be counted.

Rick


"Peo Sjoblom" wrote in message
...
It would still be 3, the first occurrence of def has a value in F


--
Regards,

Peo Sjoblom



"Joe Gieder" wrote in message
...
I don't understand how it could be 3 when I don't want it to count the def
because one of the values next to def in column F is blank. I have 3
different unique values in column P you are correct, abc, def and ghi.
Only
abc and ghi should be counted though since they all have a value in
column F.

Thanks you for your help.
Joe

"Teethless mama" wrote:

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<""),MAT CH(P1:P8,P1:P8)),MATCH(P1:P8,P1:P8))0,1))

ctrl+shift+enter, not just enter


"Joe Gieder" wrote:

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

Is what I am trying to achieve here an impossibility?

"Joe Gieder" wrote:

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 cant 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 Im looking for is 2 because Im 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