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

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

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

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

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

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

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

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

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