Logicial formula : AND of multiples OR

Question

I am trying to type a logical formula like that :

=AND(OR(A1=0;B1);OR(A2=0;B2);OR(A3=0;B3);...;OR(An=0;Bn))

But I own multiples rows and I am searching for a faster way to achieve this operation.

Any idea ?

score 1 · Accepted Answer · answered Dec 17 '20 at 22:36

You can use ARRAYFORMULA()

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

Sample Formula:

=AND(ARRAYFORMULA(IF(A2:A8=0,1,0)+(B2:B8)))

what this formula does is performing OR operation using "+" for Column A and Column B. Once the OR operation was performed, ARRAYFORMULA will return an array of results which you can use in your AND operation.

Example:

In the first scenario, when you performed an OR operation within the ARRAYFORMULA (See Cell C2, formula was shown in Cell C1) It will return an array of OR operation results per row. Then if you check the formula used in Cell D2, we just get the AND operation of the array results from ARRAYFORMULA

NOTE:

You cannot use AND() or OR() functions inside the ARRAYFORMULA(), instead we use '*' for AND() and '+' for OR().

1 Answers1