Help creating a formula

[Guest]

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 Phillips]

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

 

[PCLIVE]

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.

--

 

[Guest]

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

 

[Guest]

The answer should be 3 not 2

=SUM(IF(FREQUENCY(IF((A1:A8="001")*(F1:F8<>""),MATCH(P18,P18)),MATCH(P18,P18))>0,1))

ctrl+shift+enter, not just enter

 

[Guest]

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

 

[Guest]

Is what I am trying to achieve here an impossibility?

 

[Peo Sjoblom]

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

 

[PCLIVE]

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.

--

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Harlan Grove]

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

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

If your table above were in A29, try the array formula

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

 

[PCLIVE]

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.


--

 

[Gord Dibben]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Harlan Grove]

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.