FormuladrillAND, OR, NOT
AND(test1, test2, ...) is TRUE only when every test is TRUE; OR(test1, test2, ...) is TRUE when at least one is; NOT(test) flips TRUE to FALSE. They are used as the logical_test of IF or in conditional formatting: =IF(AND(A2="North", B2>1000), "Bonus", "None").
Syntax
AND(logical1, [logical2], ...) OR(logical1, [logical2], ...) NOT(logical)| Part | What it is |
|---|---|
logical1, logical2... | Up to 255 conditions, each TRUE or FALSE, such as A2>0, B2="Yes", ISBLANK(C2). |
logical (NOT) | One condition to reverse. |
Examples
=AND(A2>0, B2>0)Result: TRUE when both A2 and B2 are above 0
=IF(OR(A2="North", A2="South"), "East coast", "Other")Result: East coast for North or South, otherwise Other
=IF(AND(B2>=1000, C2="Shipped"), B2*0.05, 0)Result: 5% of B2 only when the order is 1000 or more and shipped
=NOT(ISBLANK(A2))Result: TRUE when A2 has something in it
Common mistakes
- Writing =IF(A2>0 AND B2>0, ...) gives an error. In Excel AND is a function: =IF(AND(A2>0, B2>0), ...).
- A2="North" OR "South" does not test both; write OR(A2="North", A2="South").
- AND and OR do not work element by element on arrays; in SUMPRODUCT or FILTER use * for AND and + for OR.
- A between test needs two comparisons: AND(A2>=10, A2<=20), not 10<=A2<=20.
Practice AND, OR, NOT
These drills check your answer on live data, free, no account needed.
- Bonus for the northA rep earns a bonus of 5% of sales only if sales are at least 50,000 AND the region is "North". Otherwise the bonus is 0. Fill D2 and D3.
- Flag for reviewFlag an invoice as "Review" if it is more than 30 days overdue OR the amount is above 10,000. Otherwise show "OK". Fill D2 to D4.
Questions
What is the difference between AND and OR?
AND needs all conditions true. OR needs any one of them true. AND(TRUE, FALSE) is FALSE, OR(TRUE, FALSE) is TRUE.
How do I test if a cell is between two values?
=AND(A2>=10, A2<=20), or inside IF: =IF(AND(A2>=10, A2<=20), "In range", "Out").
How do I use AND or OR in conditional formatting?
Choose Use a formula to determine which cells to format and enter the AND or OR formula for the first cell, for example =AND($B2>1000, $C2="Shipped").
Related
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
- IFSHow the Excel IFS function checks several conditions in order and returns the first match, with grade and shipping tier examples and the fix for #N/A.
- ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.