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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
AND, OR, NOT execute identically in every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. 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). Excel for the web is a separate application with its own calculation engine, and that one we do execute: 17 of the 18 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01). The exception is =AND(TRUE,"abc"), which returned TRUE on the web engine where Microsoft documents #VALUE!; with no desktop run we cannot say whether the web engine diverges or the documentation is wrong about both. The per-case values are on the individual function pages.
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.