Confident Cells

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.

Your turn

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.

ABCDEFG
1EmployeeAttendanceSystemsCustomer careConcern?Below 40 in either?60+ in both?
2Grace97%6872NoYes
3Tom86%3544YesNo
4Priya99%8177NoYes
5Marek91%3852YesNo
6Nia95%6159NoNo
7Ollie84%4231YesNo
8Lucy93%5566NoNo
9Hamza88%2937YesNo
10Rachel98%7463NoYes
11Sunil89%4745NoNo

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

ABCDE
1NameHoursRatingBonus?Check in?
2Amira405
3Ben304
4Cara422
5Dev251
  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.
  2. =AND(B2>=35,C2>=4) in . Cara worked 42 hours, but her rating is 2. One test is false, so AND gives FALSE.
  3. =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

ABCDE
1NameHoursRatingBonus?Check in?
2Amira405
3Ben304
4Cara422
5Dev251
  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.
  2. =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

  1. 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.
  2. In F2, show Yes if the employee scored below 40 in Systems or Customer care (or both).
  3. In G2, show Yes if the employee scored at least 60 in both Systems and Customer care.
  4. In H1, count the people who are in either IT or Operations.