SUMPRODUCT and OR

[Dave F]

I need to test for either of two conditions in the same array. The
following SUMPRODUCT formula returns errors: =SUMPRODUCT((--OR(A$1:A
$248="7DCNA008"),--(A$1:A$248="7DCNA008A")),--(J$1:J$248="PO Not in
FAS"))

Essentially, I want to test for 7DCNA008 or 7DCNA008A in the range
A1:A248. How do I do this?

Dave

 

[Bob Phillips]

=SUMPRODUCT(--((A$1:A$248="7DCNA008")+(A$1:A$248="7DCNA008A")),--(J$1:J$248="PO
Not in G4 FAS"))

--
---
HTH

Bob


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

 

[Carim]

Hi,

Give a try to following :

=SUMPRODUCT(--OR((A$1:A$248="7DCNA008"),--(A$1:A$248="7DCNA008A"))*--(J
$1:J$248="PO Not in FAS"))

HTH

 

[Bob Phillips]

That doesn't work! The OR resolves to a single TRUE if any of A1:A248 holds
that string which effectively counts how many of J1:J248 equal its string if
ANY of A1:A248 equals its, no consideration of matching the pairs.

You might be thinking of something like

=SUMPRODUCT((A$1:A$248={"7DCNA008","7DCNA008A"})*(J$1:J$248="PO Not in G4
FAS"))



--
---
HTH

Bob


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

 

[Dave F]

Thanks, this helps.

=SUMPRODUCT(--((A$1:A$248="7DCNA008")+(A$1:A$248="7DCNA008A")),--(J$1:J$248-="PO
Not in G4 FAS"))

--
---
HTH

Bob

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








- Show quoted text -