Topic 4 of 15, EHL Maths & Excel final (AP)
#
COUNTIF and COUNTIFS
Use COUNTIF or COUNTIFS when the question asks “how many?” for a specific condition or several conditions. One condition: COUNTIF. Two or more: COUNTIFS.
When the exam asks for it
- “how many guests…”, “number of bookings where…”, “count the rooms that…”
Formulas
One condition
=COUNTIF(range, criteria)Two or more conditions
=COUNTIFS(range1, condition1, range2, condition2, ...)Two conditions
=COUNTIFS(B:B, "Elite", C:C, ">=800")Three conditions
=COUNTIFS(A:A, "Geneva", C:C, ">=800", D:D, ">=3")Condition taken from a cell
=COUNTIF($C$2:$C$7, ">="&$F$1)Worked example
D8fx
=COUNTIFS($B$2:$B$7, "Elite", $C$2:$C$7, ">=800")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | City | Tier | Spend (CHF) | Nights | |
| 2 | Geneva | Elite | 920 | 3 | |
| 3 | Lausanne | Classic | 450 | 1 | |
| 4 | Geneva | Classic | 810 | 4 | |
| 5 | Zurich | Elite | 760 | 2 | |
| 6 | Geneva | Elite | 640 | 5 | |
| 7 | Lausanne | Elite | 1,050 | 3 | |
| 8 |
Sheet1
⬇ Download practice ExcelExample, Practice and Answers sheets with working formulas. Free.
Step by step
=COUNTIF($B$2:$B$7, "Elite")4 Elite guests=COUNTIFS($B$2:$B$7, "Elite", $C$2:$C$7, ">=800")2 (920 and 1,050)=COUNTIFS($A$2:$A$7, "Geneva", $C$2:$C$7, ">=800", $D$2:$D$7, ">=3")2 (920 with 3 nights, 810 with 4 nights)
⚠ Exam traps
- COUNTIFS works in pairs: every condition must have its own range (range1 + condition1, then range2 + condition2).
- Criteria with symbols >, <, >=, <= go inside quotation marks: ">=800".
- “At least 800” includes 800 (>=); “more than 800” does not (>).
- All ranges in COUNTIFS must have the same size.
- Lock the ranges with $ ($B$2:$B$7) when you copy the formula, so the table does not move.
Practice question
Using the table, how many guests are from Geneva?
Show answer
✓ =COUNTIF($A$2:$A$7, "Geneva") = 3.
Stuck on this topic before your exam? We explain it one-to-one on your own Excel files, live online. Short crash courses before the EHL Maths & Excel final are possible on weekdays and weekends.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.