📗 Excel & Google Sheets for Students · Lesson 3 of 7
IF, COUNTIF and AVERAGEIF
Label results and summarize by condition.
IF
=IF(C2>=70, "Pass", "Fail")
=IF(B2="", "Missing", B2*2)IF(test, value if true, value if false). Text goes in quotation marks.
Nested IF and IFS
=IFS(C2>=90,"A", C2>=80,"B", C2>=70,"C", C2>=60,"D", TRUE,"F")IFS checks conditions in order and returns the first true one. TRUE at the end is the “everything else” case.
Counting and summing by condition
| =COUNTIF(D2:D200, "Yes") | How many survey answers were Yes |
|---|---|
| =COUNTIF(C2:C40, ">=90") | How many scores are 90 or more |
| =AVERAGEIF(B2:B200, "Biology", C2:C200) | Average score for Biology majors |
| =SUMIF(A2:A50, "Food", B2:B50) | Total spent on food |
| =COUNTIFS(B:B,"Senior", C:C,">=3.5") | Several conditions at once |
Tip
💡 Put the condition in a cell (like G1 = "Biology") and use AVERAGEIF(B:B, G1, C:C). Change G1 and the result updates — a mini dashboard.
✏️ Practice: your turn
- Label exam scores Pass/Fail with IF
- Convert scores to letter grades with IFS
- Count “Yes” answers in a survey column with COUNTIF
- Average a score by group with AVERAGEIF
✏️ Online spreadsheet: practice formulas without installing anything
🧠 Quick quiz
Answer all 4 questions correctly to mark this lesson as done.
0 of 4 correct
All lessons in Excel & Google Sheets for Students
- 🎓 1. A grade tracker with weighted averagesSUMPRODUCT, absolute references and “what do I need on the final?”
- 🧪 2. Cleaning and formatting lab dataTables, number formats, rounding, sorting and filtering
- 🔀 3. IF, COUNTIF and AVERAGEIFLabel results and summarize by condition
- 🔍 4. XLOOKUP and VLOOKUPPull matching data from another table
- 🧮 5. Pivot tablesSummarize hundreds of rows in a few clicks
- 📊 6. Charts for reportsPick the right chart, label it and keep it clean
- 💵 7. Project: a student budgetIncome, expenses, categories, a pivot and a chart