The AND and OR functions in Excel
AND and OR let one IF check several conditions at once.
What AND / OR does
AND is true only when every test inside it is true. OR is true when at least one test is true. Both return TRUE or FALSE, so they are normally placed inside IF as its test.
Syntax
=AND(logical1, [logical2], ...) =OR(logical1, [logical2], ...)
- logical1
- The first comparison, such as B2>=60.
- logical2, ...
- Optional. More comparisons.
Worked example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Attendance | Pass? |
| 2 | Ana | 72 | 90 | Yes |
| 3 | Ben | 48 | 95 | No |
| 4 | Cy | 64 | 40 | No |
| 5 | Di | 91 | 85 | Yes |
D2 =IF(AND(B2>=60,C2>=50),"Yes","No") (copied down 4 rows)
A student passes only if the score is at least 60 and attendance is at least 50.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
HR
Check eligibility rules that need two things to be true.
Finance
Flag items that are over budget and overdue.
Sales & Ops
Mark orders that are large or urgent.
Common mistakes
- Writing AND as a word between tests, such as B2>=60 AND C2>=50. AND is a function, so it is =AND(B2>=60,C2>=50).
- Using AND when you meant OR. Ask whether every test must pass or just one.
- Passing text instead of a comparison. The tests inside must be true or false.
The AND / OR course
- 1Combine two testsFree practice belowPractise
- 2AND with three testsComing soon
- 3OR for exceptionsComing soon
- 4NOT and negative testsComing soon
- 5Eligibility rulesComing soon
Common questions
Can AND and OR be used together?
Yes. For example =IF(AND(B2>=60,OR(C2="Yes",D2="Yes")),"Pass","Fail").
Do AND and OR work outside IF?
Yes. On their own they simply return TRUE or FALSE.