| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to OR() to a SUMPRODUCT
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
Try one of these:
=SUMPRODUCT(--(AQ233:AQ2000=AQ232),--(ISNUMBER(MATCH(AA233:AA2000,{"p0","p1"},0)))) =SUMPRODUCT((AQ233:AQ2000=AQ232)*(AA233:AA2000,{"p 0","p1"})) Although the first formula is longer it is more efficient that the shorter formula. -- Biff Microsoft Excel MVP "nastech" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
Try it as:
=SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
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.. "Max" wrote: Try it as: =SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
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.. "T. Valko" wrote: Try one of these: =SUMPRODUCT(--(AQ233:AQ2000=AQ232),--(ISNUMBER(MATCH(AA233:AA2000,{"p0","p1"},0)))) =SUMPRODUCT((AQ233:AQ2000=AQ232)*(AA233:AA2000,{"p 0","p1"})) Although the first formula is longer it is more efficient that the shorter formula. -- Biff Microsoft Excel MVP "nastech" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
ok ok ok (joe pesci:) so * sign is same as: AND() ?
and: + sigh is same as OR() ??? "Max" wrote: Try it as: =SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
Yes!
-- Biff Microsoft Excel MVP "nastech" wrote in message ... ok ok ok (joe pesci:) so * sign is same as: AND() ? and: + sigh is same as OR() ??? "Max" wrote: Try it as: =SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
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 "Max" wrote: Try it as: =SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to OR() to a SUMPRODUCT
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,{"p 0","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 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. -- Biff Microsoft Excel MVP "nastech" wrote in message ... 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 "Max" wrote: Try it as: =SUMPRODUCT((AQ233:AQ2000=AQ232)*((AA233:AA2000="p 0")+(AA233:AA2000="p1"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "nastech" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumproduct | New Users to Excel | |||
| sumproduct | Excel Discussion (Misc queries) | |||
| sumproduct | Excel Discussion (Misc queries) | |||
| Sumproduct | Excel Worksheet Functions | |||
Linear Mode
