If your ARRAYFORMULA with an IF statement is “not working,” it’s likely because you are using row-level logical operators (like AND or OR) within the array context. Row-level operators reduce the entire array to a single TRUE or FALSE before the IF can process it, causing the formula to only output in the first cell. To fix this, replace row-level logical operators with boolean math: + for OR logic, and * for AND logic.
Excel
=ARRAYFORMULA(IF((A2:A > 10) * (B2:B = "Qualified"), "Process", "Skip"))The Syntax Breakdown
ARRAYFORMULA(...): This instructs Google Sheets to process the enclosed formula not on a single row, but across an entire range, dynamically “spilling” results down the column.IF(criteria, value_if_true, value_if_false): Standard row-levelIFlogic works fine for single conditions. The problem occurs when you try to use row-level operators inside the array.- Row-Level Logic (e.g.,
AND(A2 > 10, B2 = "Yes")): Crucially, row-level functions likeANDandORare designed to look at a single row. When presented with an array (e.g.,A2:A > 10), they aggregate the entire array result first.AND( {T;F;T}, {T;T;T} )evaluates to a singleFALSE, meaning theIFonly processes one result for the top cell. - Array-Level Boolean Math (e.g.,
(A2:A > 10) * (B2:B = "Yes")): Arrays in memory are 1s (TRUE) and 0s (FALSE).*(Multiplication) mimicsAND. (1 * 1 = 1,1 * 0 = 0,0 * 1 = 0,0 * 0 = 0). Only when both conditions are1(TRUE) is the final condition1(TRUE). This preserves the array structure for theIFto process row-by-row.+(Addition) mimicsOR. (1 + 1 = 2(treated as TRUE),1 + 0 = 1,0 + 1 = 1,0 + 0 = 0).
Real-World Example: Multi-Condition Project Triage
You are an Operations Manager assigning priority status to new projects. Projects are considered high-priority only if the expected MRR is over $5,000 AND the priority flag is already set to “High” or “Critical.”
You are trying to calculate the Priority Status (Column D) automatically.
Here is your raw data table (A1:C4):
| Row | A (Project Name) | B (Expected MRR) | C (Priority Flag) |
| 1 | Alpha | $12,000 | Low |
| 2 | Beta | $4,500 | High |
| 3 | Gamma | $9,000 | Critical |
The Application (Why Row-Level Logic Fails): You try to enter this common (incorrect) formula in cell D2:
Excel
=ARRAYFORMULA(IF(AND(B2:B > 5000, OR(C2:C = "High", C2:C = "Critical")), "High Priority", "Standard"))The Output: Cell D2 incorrectly calculates “Standard” (because Alpha fails the flag check). Cell D3 and D4 remain blank. The AND/OR functions evaluated the entire range as a single block, broken the array.
The Fix: Replace the failing AND/OR logic with boolean multiplication (*) in cell D2:
Excel
=ARRAYFORMULA(IF((B2:B > 5000) * ((C2:C = "High") + (C2:C = "Critical")), "High Priority", "Standard"))The Final Output: The formula correctly spills down, processing each row’s multi-condition logic independently.
| A (Project) | B (MRR) | C (Flag) | D (Status – Formula Output) |
| Alpha | $12,000 | Low | Standard |
| Beta | $4,500 | High | Standard |
| Gamma | $9,000 | Critical | High Priority |
Common Errors & How to Fix Them
- Formula returns
#REF!with “Result was not automatically expanded”: You manual data or text lives in the spill range, blocking the array output. Fix: Hover over the#REF!error to locate the blocking cell, navigate to that exact cell, and press Delete. - Formula calculates correctly but only outputs in the top cell: This is the primary symptom of row-level logical functions (
AND/OR/NOT) being used incorrectly inside the array. Fix: Prepend your formula with the array implicit intersection operator@to force single-row behavior before nesting it, or better, replace theAND/ORfunctions with*and+boolean math to maintain the dynamic spill. - Formula only calculates the top cell and displays an error icon: You are using older Google Sheets syntax (like
Ctrl+Shift+Enterfor an array formula) with new row-level input references, causing a syntax mismatch. Fix: Clear the editor, type only the formula starting withIF(no implicit operations like@), highlight the entire range reference, and pressCtrl+Shift+Enterto let Google Sheets wrap it perfectly:=ARRAYFORMULA(IF(A2:A > 10, ...)).
