Fix AND Function Failing in Excel Array Formulas
Question details
The user needs to evaluate multiple criteria row-by-row in an array formula, but the standard AND function is not working as expected.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Trying to find the earliest matching time using multiple full-column array criteria.
- Observed behavior
- The AND function evaluates the entire array as a single TRUE or FALSE value instead of processing each row separately, returning incorrect calculations.
Verify that all your referenced data ranges are the exact same size, and try to avoid using entire column references (like A:A) in array formulas, as they can severely slow down calculation speeds.
Use Boolean Multiplication (*) Instead of the AND Function
Boolean multiplication forces the spreadsheet to evaluate multiple conditions row-by-row, returning 1 for TRUE and 0 for FALSE for each individual row.
In an array formula, the standard AND function aggregates all logical tests into a single boolean result. To force row-by-row evaluation, you must multiply your criteria arrays together. When multiplying, TRUE evaluates to 1 and FALSE evaluates to 0. Only rows where all conditions are TRUE will yield a 1.
Click on the cell where you want the final calculated result (such as the minimum time) to appear.
Type your formula structure, replacing the AND function with an asterisk (*). For example: =MIN(IF((Data!L:L=Answer!H1)*(Data!G:G=Answer!A2),TIMEVALUE(Data!M:M))).
Press Ctrl+Shift+Enter on your keyboard simultaneously. The formula bar will display curly braces {} around your formula, indicating it is processing the array.
Use the FILTER Function in Newer Versions
For Microsoft 365 or newer spreadsheet software, the FILTER function handles dynamic arrays natively without requiring special keystrokes.
Calculate using the AGGREGATE Function
The AGGREGATE function can natively process arrays and ignore error values, bypassing the limitations of the standard AND function.
Master Array Formulas with WPS Spreadsheets
WPS Spreadsheets fully supports complex array formulas, dynamic arrays, and boolean logic. Easily troubleshoot and calculate row-by-row criteria without limitations, all within a familiar and intuitive interface.
- 1. Open your workbook: Launch WPS Spreadsheets and open your existing document containing the raw data and criteria.
- 2. Enter the boolean formula: Click on the target cell and type your formula, using the asterisk (*) to multiply your conditions instead of relying on the AND function.
- 3. Execute the array: Press Ctrl+Shift+Enter on your keyboard. WPS Spreadsheets will automatically wrap your formula in curly braces and calculate the array accurately.
Frequently Asked Questions
Why does the OR function also fail in Excel array formulas?
Similar to the AND function, the OR function is designed to evaluate an entire array and return a single aggregated TRUE or FALSE result. To simulate OR conditions row-by-row in an array formula, use addition (+) between your criteria instead.
Do I always need to press Ctrl+Shift+Enter for array formulas?
In older versions of Excel, you must press Ctrl+Shift+Enter to explicitly tell the software to evaluate the formula as an array. However, in newer versions supporting dynamic arrays (like Microsoft 365) and modern builds of WPS Office, pressing simply Enter is often sufficient.
What does the asterisk (*) actually do in an array formula?
The asterisk acts as a multiplication operator that forces numerical evaluation of boolean arrays. It multiplies the TRUE (1) and FALSE (0) results of your conditions. Since multiplying by zero always yields zero, only rows where all conditions are TRUE (1 * 1 = 1) will be included in your final calculation.
