AND/OR in Array Formula
 

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

AND/OR in Array Formula
 

I meant to add:

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


"Rif" wrote:

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

AND/OR in Array Formula
 

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


"Rif" wrote:

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

AND/OR in Array Formula
 

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!

"Ron Coderre" wrote:

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


"Rif" wrote:

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

AND/OR in Array Formula
 

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


"Rif" wrote:

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!

"Ron Coderre" wrote:

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


"Rif" wrote:

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

AND/OR in Array Formula
 

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


"Rif" wrote:

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

AND/OR in Array Formula
 

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

"Ron Coderre" wrote:

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


"Rif" wrote:

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!

"Ron Coderre" wrote:

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


"Rif" wrote:

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