AND() for an array

[Zilla]

I thought this would AND cells for several rows
in two columns, and put the result where the formula
is, but it doesn't. What syntax am I missing?

I have a mix of 1s & 0s in cols A and B, rows 1-10. I
expect that if a row in both A and B are 1's, col C will have
TRUE, FALSE otherwise. So my array formula in C is,

=AND(A1:A10, B1:B10)

I get FALSE for all C1:C10.

 

[Guest]

In C1: =A1*B1=1
Copy down as far as needed

or
C1: =COUNTIF(A1:B1,1)=2

 

[Zilla]

Yes thanks, but why doesn't the AND() work?

In C1: =A1*B1=1
Copy down as far as needed

or
C1: =COUNTIF(A1:B1,1)=2

 

[Bob Phillips]

Because that is not the way AND works. What exactly did you expect, TRUE if
1 is in any of a1:A10, B1:B10, TRUE if A and B is 1 (1 instance or all), or
what.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

In C1: =AND(A1=1,B1=1)
Copy down as far as needed

 

[Guest]

Another way....

Select blank cells C1:C10
=A1:A10*B1:B10

ctrl+shift+enter, not just enter

 

[Bernd]

Hello,

Yes, that's it.

Or =and(a1,b1) and copy down.

Or - if the and-function is absolutely necessary:
array-enter
=AND(INDEX(A1:A10,ROW(INDIRECT("1:10"))),INDEX(B1:B10,ROW(INDIRECT("1:10"))))

Regards,
Bernd

 

[Harlan Grove]

Yes thanks, but why doesn't the AND() work?

....

It does work according to its specs. It's handling your

=AND(A1:A10, B1:B10)

like

=AND(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,
B1,B2,B3,B4,B5,B6,B7,B8,B9,B10)

so like

=AND(AND(A1,AND(A2,AND(A3,AND(A4,A5)))),
AND(A6,AND(A7,AND(A8,AND(A9,A10)))),
AND(B1,AND(B2,AND(B3,AND(B4,B5)))),
AND(B6,AND(B7,AND(B8,AND(B9,B10)))))

That's the way AND is SUPPOSED to work. It's just not how you want it
to work. FWIW, it's no different than being disappointed that
SUM(A1:A10,B1:B10) doesn't produce the same result as A1:A10+B1:B10.

 

[Zilla]

Thanks all, I understand how AND is supposed to work
now. I'll try your ideas!