Help creating a formula
 

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

Help creating a formula
 

=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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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

Help creating a formula
 

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))))

Help creating a formula
 

That's a good point. But I was trying to refer to only the criteria in
column F, even though I didn't say so. Anyway, it looks like Harlan may be
on to something. Thanks.


--

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

Help creating a formula
 

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

Help creating a formula
 

But 2 is correct for what he has stated. Remember, he is filtering the A
column against 001. Here is the relevant part of his table...

A F P
001 1 abc
001 2 abc
001 5 abc
001 2 def
001 def
001 1 ghi

Since one of the "def" rows has a blank in column F, none of the "def" rows
are to be counted. This only leaves "abc" and "ghi" as unique column P
entries. Hence, the answer the OP is looking for is 2.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

Help creating a formula
 

Gord Dibben <gorddibbATshawDOTca wrote...
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
....

It's clear to the OP. He just expressed it poorly. At least to me it
looks like he wants something like (in Access 2003's @#$% SQL).


SELECT Count(*) AS ND_p
FROM (
SELECT T.p
FROM (
SELECT T1.p, Count(T1.f) AS nf, Count(T2.p) AS np
FROM [OP's Table] T1 INNER JOIN [OP's Table] T2 ON (T1.a=T2.a)
GROUP BY T1.a, T1.p
HAVING ((T1.a)='001')
) T
WHERE (T.nf=T.np)
);


Not something easily expressed in prose. Note: field names in SQL
above match OP's columns.