AND/OR in Array Formula

[Guest]

They seemingly do not work as anticipated. I'm probably misinterpreting
something.

=SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F$3:$F$1000<>0,$J$3:$J$1000<>0))*($H$3:$H$1000=1))

The OR section is what I am having trouble with. This formual returns
success when clearly this is not true. For example, in columns F and J, the
values are 0 but the above formula is counting them.

I do not want to use IFs. I can get it to work using IF. Still, I would
like to use ORs and ANDs.

To make it easier, strip out the

 

[Guest]

I meant to add:

To make it easier, strip out the non OR'ed chunks. Why doesn't OR and AND
work here?

 

[Guest]

Yeah....that's one of the quirks of the OR function....it returns the first
values from the list and ignores the others.

Try something like this
=SUMPRODUCT(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(($F$3:$F$1000+$J$3:$J$1000)<>0)*($H$3:$H$1000=1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Hmm.. if they were all numerical, that trick would work. They aren't. I
wish I could combine ISTEXT in there...

Thank you for the reply!

 

[Guest]

OK....You really need to post ALL of the rules and some sample data....That
way we don't have to guess things like:
When you're testing if cells <>0, you have text mixed in with numbers.

You'll get the answer you need faster and with less iterations.

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

I don't think OR and AND work well in array formulas. You could try:

=SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*((($F$3:$F$1000<>0)+($J$3:$J$1000<>0))>0)*($H$3:$H$1000=1))

 

[Guest]

You're right. I was just after the OR / AND problem. The fact that you and
JMB showed ways around the problem (the identity and other attribute
solutions): look at both columns combined. Rated all. Thanks guys.