How to Use IF in Google Sheets
=IF(logical_expression, value_if_true, value_if_false).
What IF does
IF lets a sheet make a decision. You write a test, such as "is this score at least 60?", and tell Sheets what to show when the answer is yes and what to show when it is no. It is the base for most other logic in a spreadsheet.
IF syntax
=IF(logical_expression, value_if_true, value_if_false)
logical_expression(required): a test that is TRUE or FALSE, such asB2>=60, or a reference to a cell that holds such a value.value_if_true(required): what to return when the test is TRUE.value_if_false(optional): what to return when the test is FALSE.
Google's help page calls the order of the second and third arguments "the single most common source of problems" with IF, so check that the TRUE result comes first.
A step-by-step example
Here are four test scores in cells A1:B5.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 54 |
| 4 | Cara | 67 |
| 5 | Dev | 91 |
To show Pass for scores of 60 or more and Fail for anything lower:
=IF(B2>=60, "Pass", "Fail")
For Ann's score of 82 this returns Pass. Here is why:
- Sheets evaluates
B2>=60. With 82 in B2, the answer is TRUE. - Because the test is TRUE, IF returns the second argument,
"Pass". - For Ben's 54 the test would be FALSE, so the third argument,
"Fail", would come back.
Text results go in quotation marks. Copy the formula down column C and each row is judged on its own score.
Comparison operators you can use
The test can use = (equal), <> (not equal), >, <, >= and <=. Text comparisons are also allowed, as in IF(A2="East", ...).
If you want a cell to stay visibly empty when the test fails, set the third argument to empty quotes: =IF(B2>80, "Top", "").
Nest IF for more than two outcomes
The third argument can be another IF. This gives grades A, B and C:
=IF(B2>=80, "A", IF(B2>=60, "B", "C"))
Sheets checks the first test, and only when it is FALSE does it move to the next one. Put the strictest test first. With many levels, IFS (which tests several conditions in order) is easier to read.
Common IF mistakes
- Swapped results. The TRUE result comes second and the FALSE result third.
- Missing quotation marks. Text such as
Passmust be written"Pass", or Sheets looks for a named range. - Tests in the wrong order. In a nested IF,
B2>=60placed beforeB2>=80would label every passing score B. - Numbers stored as text. A score that looks like 82 but is text may not compare as you expect.
Try it yourself
Use the score table above (A1:B5). Type your formula into the box, starting with =, then press Check.
Exercise 1: Pass or fail
In column C, write a formula for Ann's row (score in B2) that shows Pass when the score is 60 or more and Fail otherwise.
Exercise 2: Letter grade
For Cara's row (score in B4), write a nested IF that returns A for 80 or more, B for 60 or more, and C otherwise.
What to learn next
- IFS: test several conditions in order without nesting.
- COUNTIF: count the rows that match a condition.
- IFERROR: replace an error with a clean result.
- Highlight a row based on a cell: color a whole row when one cell matches.
Syntax and rules in this guide were checked against Google's IF help page. See all guides.