Excel Basics · Interactive lesson 10 of 12
IF statements
IF turns a comparison into a plain answer. Once a column of pass/fail results exists, COUNTIF and SUMIF let you ask questions about it — how many passed, and what did they score — without a single extra column.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialHow to Write IF Statements in ExcelSkill check
Try these first:
- Write an
IFformula that returns "Pass" or "Fail" based on a score. - Count how many rows meet a condition with
COUNTIF. - Total only the rows that meet a condition with
SUMIF.
Comfortable with IF but new to COUNTIF/SUMIF? That's normal — move to them only once IF feels easy.
Teach
What you're learning
=IF(B2>=70,"Pass","Fail")- A condition is a question with a yes/no answer — if 70 or above, show "Pass"; otherwise "Fail".
=COUNTIF(range, criteria)- Counts how many cells in a range match a condition, e.g.
=COUNTIF(D2:D6,"Pass"). =SUMIF(range, criteria, sum_range)- Adds up values from
sum_range, but only for rows whererangematches the condition, e.g.=SUMIF(D2:D6,"Pass",B2:B6).
Where to find it in Excel
Formulas›Logical›IF
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Student | Score | Result | ||||||
| 2 | Ana | 82 | Pass | ||||||
| 3 | Ben | 64 | Fail | ||||||
| 4 | Chris | 91 | Pass | ||||||
| 5 | Dana | 70 | Pass | ||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Formulas tab.
- 2Logical holds IF, AND, OR and IFERROR.
- 3IF opens a helper that asks for the test and both results. You can also just type =IF( in the cell.
- 4COUNTIF and SUMIF are under More Functions › Statistical and Math & Trig.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
A student score sheet. Start with the Pass/Fail formula, then move on to counting and totaling.
COUNTIF/SUMIF goes in quotes when it's text ("Pass") — but comparisons like ">=70" are also written as quoted text, since the whole condition is really one piece of text Excel parses.Real-world exercise
Grade a full class
Build a score sheet for a full class of students. Use IF to mark each as Pass or Fail. Once that feels comfortable, add two more cells: one with COUNTIF to count how many passed, and one with SUMIF to total the scores of only the students who passed.
Spot a mistake or have an idea for this lesson? Send feedback