AND vs OR: all conditions or any condition
The two workhorses of logic: AND is true only when EVERY condition is true; OR is true when AT LEAST ONE is. On their own they return TRUE/FALSE, but you'll almost always wrap them in IF to return something useful.
The differences at a glance
| AND | OR | IF | NOT | |
|---|---|---|---|---|
| True when | All conditions are true | Any one condition is true | ||
| (5>3, 5>10) | FALSE (second fails) | TRUE (first passes) | ||
| Empty / no conditions met | Needs every one | Needs just one | ||
| Inside IF | =IF(AND(a,b),x,y) | =IF(OR(a,b),x,y) | ||
| Array-math form (for SUMPRODUCT/FILTER) | multiply: (a)*(b) | add: (a)+(b)>0 | ||
| Compatibility | Universal | Universal — both verified in every version we test |
Which should you use?
- AND — Every requirement must hold: in-stock AND under-budget, date-in-range (AND of two bounds), all-fields-complete checks.
- OR — Any one qualifies: region is East OR West, status is Urgent OR Overdue, value hits any of several flags. NOT(OR(...)) means 'none of these'.
Compatibility (from executed tests)
AND, OR, NOT execute identically in every version of all three apps we test. Key limitation shared everywhere: AND/OR return a single TRUE/FALSE for the whole set — they do NOT work element-by-element inside array formulas. For per-row AND/OR across ranges (SUMPRODUCT, FILTER), use arithmetic instead: multiply conditions for AND, add them for OR (see the filter-by-multiple-criteria and sumif-vs-sumproduct recipes).
Example formulas
| All must pass | =IF(AND(A2>=0,A2<=100),"valid","out of range") |
| Any qualifies | =IF(OR(A2="East",A2="West"),"west half","east half") |
| None of these | =NOT(OR(A2="done",A2="cancelled")) |
Full per-version details on each function page: AND · OR · IF · NOT.