COUNTIFS practice exercises
COUNTIFS counts the rows that meet every condition you list. It answers questions like "How many Gadget orders came from the East?" or "How many orders had between 5 and 10 units?" in one formula.
Syntax
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
criteria_range1- The first column to test.
criterion1- A value like
"East"or a comparison like">=5". criteria_range2, criterion2...- More pairs. A row is counted only if it passes all of them.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=COUNTIFS(B2:B9,"East",C2:C9,"Gadget")
Counts the orders where Region is East and Product is Gadget. Result:
| 2 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 How many of Ana's orders had Sales over 100?
2 How many orders had between 5 and 10 Units (inclusive)?
3 How many orders were not from the East?
Master COUNTIFS in the full course
Lesson 6 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon COUNTIFS mistakes
- Ranges of different sizes, which gives an error.
- Expecting OR logic. COUNTIFS only does AND. For OR, add two COUNTIFS together.
- Writing
>=5without quotes, or">=H1"instead of">="&H1when the number is in a cell.
FAQ
How do I count between two dates or numbers?
Use the same range twice with ">=" and "<=" conditions, as in exercise 2.
COUNTIF or COUNTIFS?
COUNTIFS does everything COUNTIF does and also takes more conditions, so many people simply always use COUNTIFS.