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