AND & OR Functions
Check multiple conditions at once
AND & OR Functions
Need to check more than one condition? AND and OR are your friends!
When Do You Need These?
Real life examples:
- Give bonus IF sales > 100 AND rating > 4
- Approve loan IF income > 50000 OR has guarantor
- Pass student IF attendance > 75% AND score > 40
AND Function
Returns TRUE only if ALL conditions are true.
Formula
=AND(condition1, condition2, condition3, ...)
How AND Works
| Condition 1 | Condition 2 | AND Result |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE |
| FALSE | TRUE | FALSE |
| FALSE | FALSE | FALSE |
Think of AND as: Everyone must agree = TRUE
Example
Check if student passed: Score > 40 AND Attendance > 75
=AND(A1>40, B1>75)
| Score (A1) | Attendance (B1) | Result |
|---|---|---|
| 65 | 80 | TRUE |
| 65 | 60 | FALSE |
| 35 | 90 | FALSE |
| 35 | 50 | FALSE |
OR Function
Returns TRUE if ANY condition is true.
Formula
=OR(condition1, condition2, condition3, ...)
How OR Works
| Condition 1 | Condition 2 | OR Result |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | TRUE |
| FALSE | FALSE | FALSE |
Think of OR as: Anyone agrees = TRUE
Example
Eligible for discount: Member = "Yes" OR Purchase > 100
=OR(A1="Yes", B1>100)
| Member (A1) | Purchase (B1) | Result |
|---|---|---|
| Yes | 50 | TRUE |
| No | 150 | TRUE |
| Yes | 200 | TRUE |
| No | 50 | FALSE |
AND vs OR
| Function | Returns TRUE when | Example |
|---|---|---|
| AND | ALL conditions are true | A>5 AND B>5 → both must be >5 |
| OR | ANY condition is true | A>5 OR B>5 → either one >5 is enough |
Using AND/OR with IF
This is where the magic happens! Combine them with IF.
AND + IF
=IF(AND(condition1, condition2), "Yes", "No")
Example: Give bonus if Sales > 100 AND Rating > 4
=IF(AND(A1>100, B1>4), "Bonus", "No Bonus")
| Sales (A1) | Rating (B1) | Result |
|---|---|---|
| 150 | 5 | Bonus |
| 150 | 3 | No Bonus |
| 80 | 5 | No Bonus |
| 80 | 3 | No Bonus |
OR + IF
=IF(OR(condition1, condition2), "Yes", "No")
Example: Free shipping if Member OR Purchase > 100
=IF(OR(A1="Yes", B1>100), "Free Ship", "Pay Ship")
| Member (A1) | Purchase (B1) | Result |
|---|---|---|
| Yes | 50 | Free Ship |
| No | 150 | Free Ship |
| No | 50 | Pay Ship |
Multiple Conditions
You can check many conditions at once!
3 conditions with AND:
=AND(A1>50, B1>50, C1>50)
All three must be > 50
3 conditions with OR:
=OR(A1="Red", A1="Blue", A1="Green")
Any one color matches = TRUE
Common Mistakes
1. Forgetting to repeat cell reference
❌ =AND(A1>10, <20)
✅ =AND(A1>10, A1<20)
2. Using wrong function
- Need ALL true? → Use AND
- Need ANY true? → Use OR
3. Forgetting quotes for text
❌ =OR(A1=Yes, A1=No)
✅ =OR(A1="Yes", A1="No")
Summary
- AND = ALL conditions must be TRUE
- OR = ANY condition can be TRUE
- Combine with IF:
=IF(AND(...), "yes", "no") - Can check many conditions at once
- Always repeat cell reference in each condition