Does AND and OR work in an Array?

  • Thread starter Thread starter Gary Adamson
  • Start date Start date
G

Gary Adamson

I have an array which returns 0, although there are 22
cells in the range that match the criteria.

{=SUM(IF($O52:$O143>=0.6,IF(OR($I$4="No",AND
($I$4="Yes",$M52:$M143="o")),1)))}

This way works, but the problem is that the formula I am
using is much more complicated and will end up being much
longer due to duplicates of the same calculation for each
AND and OR criteria (where the 1 is...simplified for this
question). Is this the only way to do an array?

{=SUM(IF($O52:$O143>=0.6,IF($I$4="No",1,IF($I$4="Yes",IF
($M52:$M143="o",1)))))}
 
Hi
in array formulas AND won't work. A workaround would be to multiply the
conditions. e.g.
(rng1 = cond1)*(rng2 = cond2)

B ut in your case a SUMPRODUCT formula could be better suited. e.g.
=SUMPRODUCT(($O52:$O143>=0.6)*(M52:$M143="o"))

But you may state what you're exactly trying to do (as I did not
understand your middle conditions)
 
Gary,

On Matrices,
AND(cond1,cond2) ==> (cond1)*(cond2)
OR(cond1,cond2) ==> ((cond1)+(cond2)>0)

We can't use AND() OR() functions because they return scalar.

When the cond1 and the cond2 are EXCLUSIVE, you can omit the >0 from the OR
clause.

That's the case in your example because I4 can't be Yes and No at the same time.
Based on that, your array formula becomes:

=SUM(($O52:$O143>=0.6)*(($I$4="No")+(($I$4="Yes")*($M52:$M143="o"))))

Which reads (if I understood your prob) that you count the occurences where :
column 0 >=0.6 AND
(
I4 = No
OR
I4 = Yes AND column M = "o"
)

Regards,

Daniel M.
 
Back
Top