AND and OR: practice exercises
Combining conditions. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
Each employee has two assessment scores, Systems and Customer care. Flag a concern when attendance is below 90% and the Systems score is below 40. In E2, show Yes or No.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Attendance | Systems | Customer care | Concern? | Below 40 in either? | 60+ in both? |
| 2 | Grace | 97% | 68 | 72 | No | Yes | |
| 3 | Tom | 86% | 35 | 44 | Yes | No | |
| 4 | Priya | 99% | 81 | 77 | No | Yes | |
| 5 | Marek | 91% | 38 | 52 | Yes | No | |
| 6 | Nia | 95% | 61 | 59 | No | No | |
| 7 | Ollie | 84% | 42 | 31 | Yes | No | |
| 8 | Lucy | 93% | 55 | 66 | No | No | |
| 9 | Hamza | 88% | 29 | 37 | Yes | No | |
| 10 | Rachel | 98% | 74 | 63 | No | Yes | |
| 11 | Sunil | 89% | 47 | 45 | No | No |
How it works
AND(test1, test2…) is TRUE only if every test is true. OR(test1, test2…) is TRUE if at least one is.
They're usually used inside IF: =IF(AND(…),"Yes","No").
To count rows that match one thing or another, add two COUNTIFs. COUNTIFS always means AND.
=IF(AND(B2>=35,C2>=4),"Yes","No") =IF(OR(B2<30,C2<2),"Yes","No")
AND and OR
AND(test1, test2…) is TRUE only if every test is true. OR(test1, test2…) is TRUE if at least one is.
On their own they give TRUE or FALSE. Usually they go inside IF as the test: =IF(AND(…),"Yes","No").
Worked example: AND
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Hours | Rating | Bonus? | Check in? |
| 2 | Amira | 40 | 5 | ||
| 3 | Ben | 30 | 4 | ||
| 4 | Cara | 42 | 2 | ||
| 5 | Dev | 25 | 1 |
=AND(B2>=35,C2>=4)in . A bonus needs at least 35 hours and a rating of at least 4. Amira has 40 hours and a rating of 5: both true, so TRUE.=AND(B2>=35,C2>=4)in . Cara worked 42 hours, but her rating is 2. One test is false, so AND gives FALSE.=IF(AND(B2>=35,C2>=4),"Yes","No")in . Put AND inside IF, as the test, to show Yes or No instead.
Worked example: OR
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Hours | Rating | Bonus? | Check in? |
| 2 | Amira | 40 | 5 | ||
| 3 | Ben | 30 | 4 | ||
| 4 | Cara | 42 | 2 | ||
| 5 | Dev | 25 | 1 |
=IF(OR(B2<30,C2<2),"Yes","No")in . A manager checks in if someone worked under 30 hours or has a rating under 2. Ben worked exactly 30 and has a rating of 4: neither is true, so No.=IF(OR(B2<30,C2<2),"Yes","No")in . Dev worked 25 hours. OR only needs one test to be true, so Yes.
Counting one thing or another
COUNTIFS always means AND: every pair must match. To count rows that are in Sales or Support, add two counts:
=COUNTIF(B2:B16,"Sales")+COUNTIF(B2:B16,"Support")
That works because nobody can be in both teams, so no one is counted twice.
What you'll practise
- Each employee has two assessment scores, Systems and Customer care. Flag a concern when attendance is below 90% and the Systems score is below 40. In E2, show Yes or No.
- In F2, show Yes if the employee scored below 40 in Systems or Customer care (or both).
- In G2, show Yes if the employee scored at least 60 in both Systems and Customer care.
- In H1, count the people who are in either IT or Operations.