OR in a SUMPRODUCT formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to use OR the following SUMPRODUCT formula:

=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="RED"),--($E$2:$E$191="C"),--($I$2:$I$191<>"ACTIVE"))

What I'd like to is say if "C" or "D", so I need to introduce OR "D".

I've tried the obvious, like simply adding OR "D" or enclosing this in
brackets, or even adding ") ,--($E$2:$E$191="D"), but of course this then
looks for Cs and Ds.

This should be simple, shouldn't it?!

Thanks in advance.
 
Hi.

Try:
=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="RED"),--($E$2:$E$191="C"),--($E$2:$E$191="D"),--($I$2:$I$191<>"ACTIVE"))

It works the same as OR() :-)
 
Jon,

what you show works the same way as AND, not OR. At each record the
elements are multiplied, which corresponds to AND.

For OR you need addition:

=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="RED"),($E$2:$E$191="C")+($E$2:$E$191="D"),--($I$2:$I$191<>"ACTIVE"))

HTH
Kostis Vezerides
 
Try this:

=SUMPRODUCT(($B$2:$B$191="ProgA")*($C$2:$C$191="ProgA")*($D$2:$D$191="RED")*($E$2:$E$191={"C","D"})*($I$2:$I$191<>"ACTIVE"))
 
=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="
RED"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I$2:$I$191<>"ACTIVE")
)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Kierano said:
Hi,

I'm trying to use OR the following SUMPRODUCT formula:
Click to expand...
=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="
 
That's not correct.

That is saying if range = C AND range = D, which is a logical impossibility.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Jon von der Heyden said:
Hi.

Try:
Click to expand...
=SUMPRODUCT(--($B$2:$B$191="ProgA"),--($C$2:$C$191="ProgA"),--($D$2:$D$191="
 
Thanks for trying.

In theory this should work, as it certainly works OK when "C" is specified.

Programme Organisation Status Sens Plan
Prog A Prog A Red C
Active
Prog B Cross-prog Red D
Draft
Prog A Prog A Red C
Draft
Prog A Prog A Red D
Draft

So in the above example the calculation should report a figure of 2, as
there are 2 Prog A with a Status of Red having a Sensitivity of either C or D
with plan column not set to 'Active'.
 
Here is the general rule for changing AND to OR in Sumproduct
If you want to say: "Look for a row that reads C D" then use
=SUMPRODUCT((Range1="C")*(Range2="D"))
If you want to say: "Look for a row that has C or D in either column"
then use
=SUMPRODUCT(--(NOT((Range1<>"C")*(Range2<>"D"))))
If you want to say: "Look for either C or D in Range1" then use
=SUMPRODUCT(--(NOT((Range1<>"C")*(Range1<>"D"))))
So the rule to change from AND to OR is (DeMorgan's theorem):
Change the = to <> and negate the whole (put NOT in front)
In your example, change the formula to:
=SUMPRODUCT((Prog="ProgA")*(Org="ProgA")*(Status="RED")
*NOT((Sens<>"C")*(Sens<>"D"))*(Plan<>"ACTIVE"))
 
Works fine for me. I get 2 as predicted. Did you change it to cater for Prog
A not ProgA

=SUMPRODUCT(--($B$2:$B$191="Prog A"),--($C$2:$C$191="Prog
A"),--($D$2:$D$191="RED"),--(ISNUMBER(MATCH($E$2:$E$191,{"C","D"},0))),--($I
$2:$I$191<>"ACTIVE") )


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top