Excel AND, OR and NOT: Combine Conditions in One Formula
Combine several TRUE/FALSE tests into a single TRUE or FALSE.
IF takes one condition. These three let that one condition be several. AND returns TRUE only when every test inside it passes; OR returns TRUE when at least one does; NOT flips whatever it is given.
On their own they return bare TRUE or FALSE, which is occasionally useful in a helper column and more often a stepping stone. Their real home is the first argument of IF: =IF(AND(B2>=70, C2>=0.8), "Pass", "Refer") reads as one sentence, where the equivalent nested IF reads as a puzzle.
They also matter in conditional formatting, where a rule takes a single formula returning TRUE or FALSE. Any rule that depends on two things at once is an AND or an OR underneath.
Syntax
=AND(logical1, [logical2], …) =OR(logical1, …) =NOT(logical)Arguments
logical1, …Required- Conditions to test, up to 255. Each must evaluate to TRUE or FALSE — usually a comparison.
logicalRequired- NOT only, and it takes exactly one condition to invert.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Attendance | Course |
| 2 | Alice Moreau | 82 | 0.96 | Finance |
| 3 | Bruno Santos | 54 | 0.71 | Marketing |
| 4 | Chen Wei | 91 | 0.88 | Finance |
| 5 | Dana Okafor | 67 | 0.62 | Operations |
Worked examples
=AND(B2>=70, C2>=0.8)Result: TRUE
Both conditions hold for Alice: 82 marks and 96% attendance.
=OR(B3>=70, C3>=0.8)Result: FALSE
Neither holds for Bruno, so even the lenient OR fails.
=IF(AND(B4>=70, C4>=0.8), "Pass", "Refer")Result: Pass
The everyday shape. Chen clears both bars, so the true branch runs.
=IF(NOT(D5="Finance"), "Other faculty", "Finance")Result: Other faculty
NOT inverts a test. Here it is clearer written as D5<>"Finance", which is usually true of NOT.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Always returns TRUE
Why it happens: One of the arguments is not a comparison. AND(A2, B2) treats any non-zero number as TRUE rather than testing anything.
How to fix it: Make every argument an explicit comparison: AND(A2>0, B2="North").
#VALUE!
Why it happens: An argument is text that cannot be read as a condition.
How to fix it: Check for a stray quote or a missing operator inside one of the tests.
Cell shows TRUE instead of the answer
Why it happens: The AND was used on its own where it was meant to be inside an IF.
How to fix it: Wrap it: =IF(AND(...), "Yes", "No").
Conditional formatting rule never fires
Why it happens: Absolute and relative references are mixed up, so every row tests the same cell.
How to fix it: Write the rule for the top-left cell of the range and lock only the columns that should not move.
Tips worth knowing
- AND and OR nest inside each other: =OR(AND(a, b), AND(c, d)) expresses "either both of these or both of those".
- In array contexts multiplication is AND and addition is OR — that is what FILTER and SUMPRODUCT use, because AND collapses an array to one value.
- NOT(x=y) is almost always better written as x<>y.
- XOR returns TRUE when an odd number of conditions are true, which is the "one or the other but not both" case.
Frequently asked questions
How do I test two conditions in one IF?
Put AND inside it: =IF(AND(B2>=70, C2>=0.8), "Pass", "Refer"). Use OR in the same position when either condition should be enough. This is clearer than nesting a second IF and behaves identically.
Why does AND not work inside FILTER?
AND collapses a whole array down to a single TRUE or FALSE, which is not what FILTER needs — it wants one result per row. Multiply the conditions instead: (a)*(b) for AND and (a)+(b) for OR.
How many conditions can AND take?
Up to 255, though a formula with more than three or four is usually a sign that the logic belongs in a lookup table rather than in a single cell.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.