how to OR() to a SUMPRODUCT

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

Guest

hi, trying to test 2 colums, of 1 column: has either of 2 criteria..

=SUMPRODUCT(--(AQ233:AQ2000=AQ232),OR(--(AA233:AA2000="p0"),--(AA233:AA2000="p1"))) not quite working

singularly, does work: (but need to test for "p0" or "p1"), thanks
- how to OR() a sumproduct..

=SUMPRODUCT(--(AQ233:AQ2000=AQ232),--(AA233:AA2000="p0"))>0
 
Try one of these:

=SUMPRODUCT(--(AQ233:AQ2000=AQ232),--(ISNUMBER(MATCH(AA233:AA2000,{"p0","p1"},0))))

=SUMPRODUCT((AQ233:AQ2000=AQ232)*(AA233:AA2000,{"p0","p1"}))

Although the first formula is longer it is more efficient that the shorter
formula.
 
thank you sir.. was trying to get the brackets version to work, will try
that. ultimate goal is a conditional format, will try max's for that, thanks
both.

had been wrestling with this for awhile.. after some effort had just come
up with use of sumproduct.. would have worked:
else
=OR(SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")),SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p1")))

thanks much..
 
thank you sir.. was trying to get the brackets version to work, will try
that. ultimate goal is a conditional format, will try max's for that, thanks
both.

had been wrestling with this for awhile.. after some effort had just come
up with use of sumproduct.. would have worked:
else
=OR(SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p0")),SUMPRODUCT(--(AQ$152:AQ$1228=$AQ12),--(AA$152:AA$1228="p1")))

thanks much..
 
in case anyone wanted to know, seems to get a true-false, use e.g.:
=SUMPRODUCT((AQ152:AQ1228=AQ12)*((AA152:AA1228="p0")+(AA152:AQ1228="p1")))>0
 
You didn't say this was for use in conditional formatting.

So, this formula that I suggested would not work because you can't use array
constants in CF:

=SUMPRODUCT((AQ233:AQ2000=AQ232)*(AA233:AA2000,{"p0","p1"}))
in case anyone wanted to know, seems to get a true-false, use e.g.:
=SUMPRODUCT((AQ152:AQ1228=AQ12)*((AA152:AA1228="p0")+(AA152:AQ1228="p1")))>0
Click to expand...

Yes, that will indeed work. Just a little FYI...

Excel will evaluate *any* number other than 0 as TRUE (0 evaluates as
FALSE). So, in the above you don't actually have to test for >0 but it does
make it easier to understand what was intended.
 
Back
Top