AND/OR in Array Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rif - 21 Feb 2007 00:47 GMT

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

Reply to this Message

Rif - 21 Feb 2007 01:32 GMT

I meant to add:

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

> They seemingly do not work as anticipated. I'm probably misinterpreting
> something.
[quoted text clipped - 9 lines]
>
> To make it easier, strip out the

Reply to this Message

Ron Coderre - 21 Feb 2007 01:43 GMT

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

> They seemingly do not work as anticipated. I'm probably misinterpreting
> something.
[quoted text clipped - 9 lines]
>
> To make it easier, strip out the

Reply to this Message

Rif - 21 Feb 2007 01:49 GMT

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!

> Yeah....that's one of the quirks of the OR function....it returns the first
> values from the list and ignores the others.
[quoted text clipped - 22 lines]
> >
> > To make it easier, strip out the

Reply to this Message

Ron Coderre - 21 Feb 2007 02:02 GMT

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

> Hmm.. if they were all numerical, that trick would work. They aren't. I
> wish I could combine ISTEXT in there...
[quoted text clipped - 27 lines]
> > >
> > > To make it easier, strip out the

Reply to this Message

Rif - 21 Feb 2007 10:28 GMT

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

> OK....You really need to post ALL of the rules and some sample data....That
> way we don't have to guess things like:
[quoted text clipped - 39 lines]
> > > >
> > > > To make it easier, strip out the

Reply to this Message

JMB - 21 Feb 2007 04:39 GMT

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

> They seemingly do not work as anticipated. I'm probably misinterpreting
> something.
[quoted text clipped - 9 lines]
>
> To make it easier, strip out the

Reply to this Message