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