How to Fix AND Function Not Working in Excel Array Formulas
Question details
The user needs to evaluate multiple logical conditions independently for each row or element within an array formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing dynamic array formulas or using array-based functions like FILTER and IF that require multiple criteria to be met simultaneously.
- Observed behavior
- The AND function aggregates the entire array into a single TRUE or FALSE value instead of evaluating elements row-by-row, causing the array formula to fail or return a single scalar result.
Verify the exact ranges of the arrays you are trying to evaluate, ensuring that they are of the same size and orientation before applying logical math.
Replace AND with Logical Multiplication (Boolean Math)
The most robust and widely compatible method to evaluate multiple conditions in arrays is to multiply the logical arrays, which forces row-by-row evaluation.
Because the AND function is designed to return a single aggregate result, it breaks the array spill behavior. By multiplying conditions together, you convert TRUE/FALSE values into 1s and 0s. A 1 represents TRUE (both conditions met), and a 0 represents FALSE.
Determine the arrays and conditions you want to test. For example, testing if values in A2:A10 are less than or equal to 6, and if values in B2:B10 are greater than or equal to 4.
Enclose each separate logical test in parentheses. This ensures the comparison operations are calculated before the multiplication: `(A2:A10<=6)` and `(B2:B10>=4)`.
Use the asterisk symbol (*) to multiply the groups together. Type `=(A2:A10<=6)*(B2:B10>=4)` into the formula bar.
Place this multiplication inside your target function. For example, inside an IF statement: `=IF((A2:A10<=6)*(B2:B10>=4), "Yes", "No")`.
Use Nested IF Functions
As an alternative to multiplication, you can nest IF functions to simulate an AND condition that correctly spills as an array.
Utilize the BYROW Function (Office 365 / Modern Excel)
In modern spreadsheet software, you can use the BYROW function alongside a LAMBDA to force the standard AND function to evaluate row by row.
Master Array Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, Boolean logic, and dynamic arrays. You can effortlessly evaluate multiple conditions using multiplication without encountering formula aggregation errors.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Select the output range: Click on the cell where you want your dynamic array to start spilling.
- 3. Enter the Boolean formula: Type your logical multiplication formula, such as `=FILTER(A1:C10, (A1:A10="Apples")*(B1:B10>20))`.
- 4. Apply the array formula: Press Enter to execute. The software will automatically calculate and spill the row-by-row results.
Frequently Asked Questions
Why does the OR function also fail in array formulas?
Like the AND function, the OR function is designed to evaluate an entire range and return a single aggregate TRUE or FALSE value. To fix this, use addition (+) instead of the OR function. For example, `=(A2:A10<=6)+(B2:B10>=4)` forces a row-by-row OR evaluation.
Can I use boolean multiplication inside the FILTER function?
Yes, using logical multiplication is the standard method for applying multiple criteria in the FILTER function. In the 'include' argument, write it as `(criteria1)*(criteria2)` to act as an AND operator.
Will boolean array multiplication work in older versions of Excel?
Yes, logical multiplication has been supported in Excel for decades. However, if you are using an older version of Excel that does not support dynamic arrays, you must press Ctrl+Shift+Enter to confirm the formula, wrapping it in curly braces {}.
What if I have more than two conditions to check?
You can chain as many conditions as you need by simply multiplying them together. For example: `=(Condition1)*(Condition2)*(Condition3)*(Condition4)`.
