| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Array formula and multiplying conditions | Excel Worksheet Functions | |||
| SumProduct Formula Help | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
