How to apply a logical OR to an array in Excel

Question

I'm trying to have Excel make me an array by ORing two arrays. As an example, let two arrays A1:A3 be 7, 8, -3 and B1:B3 be 4, -8, -8.

This should be fairly simple but it appears that

OR ( A1:A3 > 0 ; B1:B3 > 0 ) 

returns TRUE instead of the array I'm expecting (TRUE, TRUE, FALSE).

Of course I could use a dirty trick like

(((A1:A3 > 0)*1 + (B1:B3 > 0)*1) >= 1) *1 

but who would want to maintain that?

Also, there is probably something to search around "CSE (Ctrl+Shift+Enter) Formulas" (http://www.mrexcel.com/articles/CSE-array-formulas-excel.php) but it really looks like black magic.

Did I miss something easy?

For those curious about it, the actual formula I'm trying to build is a little more complex of course. It's trying to count (via SUMPRODUCT) all rows where (status == A OR status == B) AND Date = some cell. The OR I'm looking for is just one array of my SUMPRODUCT and is not defined in cells (that would be too easy).

Answer 1

You can't typically use OR or AND in "array formulas" because, as you have discovered here, they return only a single result (AND is TRUE only if all conditions are TRUE, OR is TRUE is any are TRUE, as you would expect, so in your example you'll get TRUE as long as at least one of the 6 values is positive).

I'd use something similar to your suggestion, using + in place of OR effectively, although you don't need the first two *1 because the + co-erces, so this would suffice

=((A1:A3 > 0) + (B1:B3 > 0) >0) *1

although if you have a single column which can be one thing or another then that's mutually exclusive, surely, so perhaps that needs just

=(A1:A3="A")+(A1:A3="B")

Answer 2

A clean and relatively easy way around this is to use nested IF statements:

IF(A1:A3 > 0, TRUE, IF(B1:B3 > 0, TRUE, FALSE)) 

This returns TRUE if the number in A is greater than 0, then, if not, returns TRUE if the number in B is greater than 0, but otherwise returns FALSE.

Basically you are just writing your own OR function. You could do the same with AND. As long as you have conditionals and nots, you can make everything else out of them.