IF With Multiple Conditions in Google Sheets
=IF(AND(test1, test2), "yes", "no") needs every test to be true. =IF(OR(test1, test2), "yes", "no") needs at least one.
IF takes one test, so combine them
The first argument of IF is a single TRUE or FALSE. To check several conditions, wrap them in AND or OR, which each return a single TRUE or FALSE, and use that as the test.
The example
Here are four students in cells A1:C5, with a test score and an attendance percentage.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Score | Attendance |
| 2 | Ann | 82 | 95 |
| 3 | Ben | 54 | 90 |
| 4 | Cara | 67 | 70 |
| 5 | Dev | 91 | 85 |
AND: every condition must be true
Per Google's help page, AND returns TRUE only if all of its arguments are true. To pass a student only if the score is at least 60 and attendance is at least 90:
=IF(AND(B2>=60, C2>=90), "Pass", "Fail")
For Ann (82 and 95) this returns Pass. Here is why:
B2>=60is TRUE andC2>=90is TRUE.- AND returns TRUE because both are true, so IF returns
"Pass".
For Cara (67 and 70), attendance fails the second test, so AND is FALSE and the result is Fail.
OR: at least one condition must be true
OR returns TRUE if at least one argument is true. To give an award for a score of 90 or more, or attendance of 95 or more:
=IF(OR(B5>=90, C5>=95), "Award", "")
For Dev (score 91) this returns Award. The empty quotes mean the cell stays blank when neither test passes.
Mixing AND and OR
You can nest them: AND(B2>=60, OR(C2>=90, D2="Excused")). Use brackets carefully and add one test at a time.
Nested IF or IFS?
AND and OR answer "do these conditions hold together?" with one result. When you need different results for different cases, such as grades A, B and C, use a nested IF or the IFS function.
Common mistakes
- Writing two tests in IF without AND.
IF(B2>=60, C2>=90, ...)treats the second test as the TRUE result. - Using AND when you mean OR. AND needs everything to be true, so it is the stricter of the two.
- Numbers as truth values. Google's page notes that 0 counts as FALSE and any other number as TRUE.
Try it yourself
Use the table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Both must pass
For Ann's row, write an IF that returns Pass only when the score in B2 is at least 60 and the attendance in C2 is at least 90, and Fail otherwise.
Exercise 2: Either one
For Dev's row, write an IF that returns Award when the score in B5 is at least 90 or the attendance in C5 is at least 95, and stays blank otherwise.
What to learn next
- IF: run a test and return one of two results.
- IFS: test several conditions in order without nesting.
- IFERROR: replace an error with a clean result.
- AND, OR and NOT: the logical functions behind these tests.
Syntax and rules in this guide were checked against Google's IF with multiple conditions help page. See all guides.