The guide · AND, OR
Syntax
=AND(logical1, [logical2], …)
=OR(logical1, [logical2], …)Each argument is a test that is TRUE or FALSE, like B2>=50000. You can give them up to 255 tests, but two or three is normal.
Which one?
| Sales ≥ 50,000 | Rating ≥ 4 | AND | OR |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
Read the rule out loud. "Sales of 50,000 and a rating of 4" is AND. "Sales under 30,000 or a rating under 3" is OR. The two only disagree when exactly one test is true, so that's the row to check.
A worked example
Dua sold 75,000 but has a rating of 2.5. For the bonus, =AND(B5>=50000, C5>=4) is FALSE: one test out of two isn't enough. For coaching, =OR(B5<30000, C5<3) is TRUE: the rating alone is enough.
Words instead of TRUE and FALSE
To show "Yes" or "No", put AND or OR inside an IF: =IF(AND(B2>=50000, C2>=4), "Yes", "No"). Close the AND bracket after the last test and before the "Yes". There's also XOR, which is TRUE when exactly one test is true, and NOT, which flips TRUE and FALSE.
Common mistakes
- OR when the rule needs both. Someone who meets one condition gets TRUE. Check a row where only one test is true.
- > instead of >=. "50,000 or more" includes exactly 50,000.
- Writing the tests in words.
=AND(B2>=50000 and C2>=4)doesn't work. The tests are separated by commas inside the brackets. - "TRUE" in quotes.
=IF(AND(…), "TRUE", "FALSE")gives text that only looks like TRUE. AND already returns the real value.
FAQ
- How do I use AND and OR in Excel?
- Give them tests separated by commas. =AND(B2>=50000,C2>=4) is TRUE when both are true; =OR(B2<30000,C2<3) is TRUE when either one is.
- What is the difference between AND and OR in Excel?
- AND needs every test to be true. OR needs at least one. They give different answers only when some tests are true and others are false.
- How do I use IF with AND in Excel?
- Put AND as the test of the IF: =IF(AND(B2>=50000,C2>=4),"Bonus","No bonus").
- Can I combine AND and OR in one formula?
- Yes, by nesting them: =AND(B2>=50000,OR(C2>=4,D2="Yes")) is TRUE when sales are high enough and at least one of the other two tests is true.