IF Function in Excel: Simple Examples You Can Copy
Learn the Excel IF function with simple examples: pass/fail, over budget, bonuses, text and number tests, and how to combine IF with AND, OR and other formulas.
By Ayoub Farid · · 2 min read
IF is how a spreadsheet makes decisions. It checks a condition and returns one value if it's true and another if it's false. It's one of the most searched Excel functions, and it's in almost every job test.
The syntax
=IF(logical_test, value_if_true, value_if_false)- logical_test: a question with a yes/no answer, like
B2>=5000 - value_if_true: what to show if yes
- value_if_false: what to show if no
Example 1: target hit or not
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Status |
| 2 | Jan | 4,200 | |
| 3 | Feb | 5,600 |
In C2:
=IF(B2>=5000,"Target hit","Below target")Fill down. Jan shows "Below target", Feb shows "Target hit".
Comparison operators
| Operator | Means |
|---|---|
= | equal to |
<> | not equal to |
> / < | greater / less than |
>= / <= | greater / less than or equal |
Text must be in double quotes: =IF(C2="Paid","Done","Chase"). Text comparisons ignore case.
Example 2: return a calculation
IF can return numbers or formulas, not just text. A 5% bonus only above target:
=IF(B2>=5000,B2*5%,0)Example 3: leave a cell blank
=IF(B2="","",B2*C2)If there's no price yet, show nothing instead of 0.
Example 4: two conditions
Use AND or OR inside the test:
=IF(AND(B2>=5000,C2<3000),"Great month","OK")
=IF(OR(D2="Late",E2="Missing"),"Check","Fine")More in AND and OR.
More than two outcomes
For grades like A/B/C, nest IFs or use IFS:
=IF(B2>=90,"A",IF(B2>=80,"B","C"))
=IFS(B2>=90,"A",B2>=80,"B",TRUE,"C")Common mistakes
- Missing quotes around text:
=IF(C2=Paid,...)gives#NAME?. - Quotes around numbers:
=IF(B2>"5000",...)compares as text and gives wrong results. - Wrong order in nested IFs: test the highest threshold first.
- Typing the threshold in every formula: put 5000 in a cell and use
$F$1so you can change it once. See absolute references.
Related: count or sum by condition
If you want to count or add rows that meet a condition, you don't need IF in every row. Use COUNTIF and SUMIF.
Practise IF
IF, AND and OR are the next unit coming to Cellio ("Decisions"). Until it ships, the live units teach the references and $ locks you'll need in every IF you fill down.
Frequently asked questions
How do I write an IF formula in Excel?
=IF(test, value if true, value if false), for example =IF(B2>=50,"Pass","Fail").
Can IF return a blank cell?
Yes, use two double quotes: =IF(B2="","",B2*2).
How many IFs can you nest in Excel?
Up to 64, but more than three gets hard to read. Use IFS or a lookup table instead.
Is IF the same in Google Sheets?
Yes, =IF(test, true, false) works identically.