Sumproduct formula help needed 1

[knifey]

Hi,
Formula trouble! Can anyone advise on how to change the following formula to count ONLY ONCE if both columns Q and U are <> ""?

=SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002="NA")*(master!A3:A2002<>"5")*(master!Q3:Q2002<>"")*(master!W3:W2002="")*(master!AQ3:AQ2002="1"))+SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002<>"NA")*(master!A3:A2002<>"5")*(master!AQ3:AQ2002="1")*(master!W3:W2002="")*(((master!Q3:Q2002<>"")+(master!U3:U2002<>""))>=1))

The first part is working correctly:
SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002="NA")*(master!A3:A2002<>"5")*(master!Q3:Q2002<>"")*(master!W3:W2002="")*(master!AQ3:AQ2002="1"))

But the second part is giving the wrong values:
+SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002<>"NA")*(master!A3:A2002<>"5")*(master!AQ3:AQ2002="1")*(master!W3:W2002="")*(((master!Q3:Q2002<>"")+(master!U3:U2002<>""))>=1))

Any advice would be much appreciated.

Thanks,
K

 

[SkipVought]

hi,

I must be missing something.

No matter if I have a value in Q or U or both, I get the same result. Isn't that what you want?

What are the values you have in the row that gives you the "wrong" result?

[highlight]Please COPY the row of values AS IS and PASTE into your reply. That way I can simply copy your ROW and PASTE into my sheet in one operation.[/highlight]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

No, what I need is for it to count only if column Q AND U are not blank.
Column Q and U contain dates, so I'm looking for a count of rows where both dates are <> "" (on top of my other criteria).

Thanks,
K

 

[SkipVought]

Then that's simply
[tt]
*(master!Q3:Q2002<>"")*(master!U3:U2002<>"")
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Then the answer for those two in the second function are...
[tt]
="")*(master!Q3:Q2002<>"")*(master!U3:U2002<>""))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[byundt]

In a SUMPRODUCT formula, you multiply criteria to get the equivalent of an AND on a row by row basis.
=SUMPRODUCT((master!C3:C2002="Rx")*(master!R3:R2002="NA")) counts the rows where both criteria are satisfied

Getting an OR is a little trickier. You need to add the criteria, then test whether the sum is 1 or more on a row by row basis.
=SUMPRODUCT(((master!C3:C2002="Rx") + (master!R3:R2002="NA"))>=1)*1) counts the rows where either criteria is satisfied

When you restated your question as "what I need is for it to count only if column Q AND U are not blank" then you need to change from an OR combination to an AND. That's why SkipVought's most recent suggestions ought to be working for you.

 

[knifey]

Thank you both for the excellent explanation.

I did originally try (master!Q3:Q2002<>"")*(master!U3:U2002<>"") but this was giving an incorrect result due to column AQ having the dreaded #VALUE errors on a few rows.

Cheers,
K