OR in a SUMPRODUCT formula
 

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.

OR in a SUMPRODUCT formula
 

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() :-)
--
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"Kierano" wrote:

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.

OR in a SUMPRODUCT formula
 

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

Jon von der Heyden wrote:
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() :-)
--
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"Kierano" wrote:

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.

OR in a SUMPRODUCT formula
 

Try this:

=SUMPRODUCT(($B$2:$B$191="ProgA")*($C$2:$C$191="Pr ogA")*($D$2:$D$191="RED")*($E$2:$E$191={"C","D"})* ($I$2:$I$191<"ACTIVE"))



"Kierano" wrote:

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.

OR in a SUMPRODUCT formula
 

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

OR in a SUMPRODUCT formula
 

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" wrote in message
...
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() :-)
--
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"Kierano" wrote:

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.

OR in a SUMPRODUCT formula
 

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


"Bob Phillips" wrote:

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

OR in a SUMPRODUCT formula
 

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

OR in a SUMPRODUCT formula
 

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)

"Kierano" wrote in message
...
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'.


"Bob Phillips" wrote:


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

OR in a SUMPRODUCT formula
 

Doh! You are absolutely correct! Sorry if I mislead...!
--
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"vezerid" wrote:

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

Jon von der Heyden wrote:
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() :-)
--
Two heads are better than one!

Email: subst1tut3 numb3rs for l3tt3rs...


"Kierano" wrote:

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.