ArrayFormula and "AND" Formula in Google Sheets

Google SheetsArray Formulas

Google Sheets Problem Overview


In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be.

A|B
2|7

In C1 I put formula as: =and(A1>5,B1>6) then I get True. If in D1 I put formula as: =ArrayFormula(and(A1:A>5,B1:B>6)) I get the results as False.

Here are my two questions:

  1. Why is ArrayFormula not repeated for all cells in the column?
  2. Why do I get true without ArrayFormula and False with Arrayformula?

Google Sheets Solutions


Solution 1 - Google Sheets

AND doesn't work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions.

I.e. it checks if "">"" which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down.

You can use multiplication of truth values to create ANDing that works with ARRAYFORMULA like this:

=ArrayFormula((A1:A>1)*(B1:B>6) = 1)

The OR equivalent would obviously be

=ArrayFormula((A1:A>1)+(B1:B>6) > 0)

Solution 2 - Google Sheets

Another a little simpler way:

=ARRAYFORMULA(
  IF(H3:H = I3:I = J3:J = TRUE; 1; 0)
)

Solution 3 - Google Sheets

AND doesn't go with an Arrayformula. However, there is an interesting way to use AND and OR operators without really using them.

While using arrayformula, in order to do an AND function you must multiply the 2 conditions, for example

=arrayformula(if((condition1)*(condition2), value if true, value if false)) 

Similarly using OR functions is identical EXCEPT instead of a "*" Symbol you would ADD the 2 conditions.

=arrayformula(if((condition1)+(condition2), value if true, value if false)) 

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionHosseinView Question on Stackoverflow
Solution 1 - Google SheetsRobin GertenbachView Answer on Stackoverflow
Solution 2 - Google SheetsQwertyView Answer on Stackoverflow
Solution 3 - Google SheetsFarhan KhanView Answer on Stackoverflow