Why does the AND function collapse the result into a single value when using named ranges?

Question

Question

I'm using named ranges extensively in my workbook, but now find that they are not a simple substitute for regular ranges in the case of the AND function. In that case the formula is no longer "spilled" over to subsequent rows and only 1 value is calculated. How can I fix that behavior?

Dummy data

This is my dummy data:

colu1	colu2
3	0
0	2
1	9
2	1

Attempts

Successfully using single condition

I want to check if both colu1 and colu2 are not equal to zero. Checking this for a single column works as expected.

Formula's (the formula "spills" over by itself, I don't have to drag it down) on the left and result on the right:

colu1<>0?	colu2<>0?		colu1<>0?	colu2<>0?
=IF(colu1<>0, 1, 0)	=IF(colu2<>0, 1, 0)	→	1	0
(SPILLS)	(SPILLS)	→	0	1
(SPILLS)	(SPILLS)	→	1	1
(SPILLS)	(SPILLS)	→	1	1

Failing when using multiple conditions

Checking both columns at the same time in an AND formula fails:

Formula's (in this case the formula does not "spill" anymore) on the left, result on the right:

both<>0?		both<>0?
=IF(AND(colu1<>0, colu2<>0), 1, 0)	→	0
(NO SPILL)	→	(EMPTY)
(NO SPILL)	→	(EMPTY)
(NO SPILL)	→	(EMPTY)

Answer 1

AND takes array inputs and outputs singular result not an array. Instead use *:

=IF((colu1<>0)*(colu2<>0), 1, 0)

Or if you have Excel 2013 or later you can use BITAND():

=IF(BITAND(colu1<>0,colu2<>0),1,0)

But note that BITAND requires two and only two arguments, and will not work with more. It works in this case as we are basically comparing 1's and 0's. This is not a silver bullet for using AND in Array formulas. And thus the best method remains the multiplication of Booleans.

Answer 2

Or skipping IF: =(--(colu1*colu2<>0))