📎 This article includes 1 downloadable practice file ↓
In this article
Until now every formula did the same thing in every row. IF lets a cell decide: if the sales reached the target, show “Met”, otherwise show “Missed”. It’s the most useful function after SUM, and once you can say the rule in plain words, you can write it.
The shape of IF
=IF(test, value_if_true, value_if_false)
=IF(B2>=C2, "Met", "Missed")Read it aloud: “If B2 is greater than or equal to C2, show Met, otherwise show Missed.” Text answers go in double quotes; numbers and formulas don’t.
Comparison operators
| Operator | Means |
|---|---|
| = | equal to |
| <> | not equal to |
| > / < | greater / less than |
| >= / <= | greater or equal / less or equal |
Simran’s sales exactly equal her target. With >= she has met it; with > she hasn’t. Pick the operator that matches your rule.
Returning numbers and calculations
=IF(B2>=C2, B2*2%, 0) ' bonus: 2% of sales if target met
=IF(B2<C2, C2-B2, 0) ' shortfall, zero when met
=IF(D2="", "Pending", "Done") ' is a cell empty?Two conditions: AND, OR
AND is true only when every condition is true. OR is true when any one is.
=IF(AND(B2>=C2, D2<=2), "Star", "") ' met target AND at most 2 days absent
=IF(OR(B2<C2, D2>3), "Review", "OK") ' missed target OR more than 3 days absentReturning "" (two quote marks with nothing between) leaves the cell looking empty.
More than two outcomes: nested IF
Rating: A if sales are at least 110% of target, B if target met, otherwise C. Put the strictest test first:
=IF(B2>=C2*1.1, "A", IF(B2>=C2, "B", "C"))Excel checks the first test; only if it’s false does it move to the second IF. With Excel 2019 or 365 you can write the same thing more readably with IFS:
=IFS(B2>=C2*1.1, "A", B2>=C2, "B", TRUE, "C")More than 3–4 levels? Use a lookup table instead (see approximate-match lookups); it’s easier to read and change.
Counting results
Once each row says Met or Missed, count them with COUNTIF: =COUNTIF(E2:E6, "Met"). Or skip the helper column: =SUMPRODUCT(--(B2:B6>=C2:C6)) counts rows where sales ≥ target.
Where beginners go wrong
| Mistake | What happens | Fix |
|---|---|---|
Text without quotes: =IF(B2>=C2, Met, Missed) |
#NAME? | “Met”, “Missed” |
Quotes around numbers: "0" |
The result is text and won’t add up | Plain 0 |
| Nested IF in the wrong order (B test before A test) | Nobody ever gets A | Strictest condition first |
=IF(B2>=400000…) with the target typed in |
Breaks when targets change | Point to the target cell |
| Comparing to “Yes” when data says “yes “ | Trailing space makes it FALSE | Clean data, or TRIM |
For more IF patterns see nested IF, AND, OR and IFERROR.
Practice
Download this lesson’s workbook below. The Team sheet has five sales reps with sales, targets and days absent. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 6 practice workbookA sales team sheet: met/missed, bonus, shortfall, star performer and rating rules.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.