COUNTIF and COUNTIFS: practice exercises
Counting rows that match. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In H2, count how many employees are in the Finance team.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Region | Attendance | Training hours | |||
| 2 | Grace | Sales | North | 97% | 64 | Staff in Finance | ||
| 3 | Tom | Finance | South | 88% | 22 | Attendance below 90% | 4 | |
| 4 | Priya | Sales | East | 99% | 81 | North region staff in Sales | 2 | |
| 5 | Marek | Support | North | 91% | 35 | 50+ hours and 95%+ attendance | 6 | |
| 6 | Nia | Finance | East | 95% | 58 | |||
| 7 | Ollie | Support | South | 84% | 17 | |||
| 8 | Lucy | Sales | South | 93% | 46 | |||
| 9 | Hamza | Finance | North | 89% | 29 | |||
| 10 | Rachel | Support | East | 98% | 73 | |||
| 11 | Sunil | Sales | North | 86% | 31 | |||
| 12 | Tanya | Finance | South | 96% | 52 | |||
| 13 | Will | Support | North | 94% | 40 | |||
| 14 | Zoe | Sales | East | 92% | 49 | |||
| 15 | Ben | Finance | North | 97% | 66 | |||
| 16 | Ella | Support | South | 90% | 38 |
How it works
COUNTIF(range, criteria) counts the cells in the range that match. Criteria go in quotes: a word such as "Finance", or a comparison such as ">=30" or "<0.9".
COUNTIFS takes pairs of range and criteria, and counts rows where every pair matches.
=COUNTIF(B2:B7,"Finance") =COUNTIF(C2:C7,">=30") =COUNTIFS(B2:B7,"Finance",C2:C7,">=30")
What COUNTIF does
COUNTIF counts the cells in a range that match a condition. How many people are in Finance? How many worked 30 hours or more?
It needs two things, separated by a comma: COUNTIF(range, criteria).
- range: the cells to look through, such as
B2:B7. - criteria: what to count, in "quotes", such as
"Finance"or">=30".
Worked example: counting a word
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Hours | Finance staff | ||
| 2 | Amira | Finance | 12 | 30+ hours | ||
| 3 | Ben | Sales | 30 | Finance and 30+ | ||
| 4 | Cara | Finance | 45 | |||
| 5 | Dev | Support | 8 | |||
| 6 | Ewa | Sales | 32 | |||
| 7 | Finn | Finance | 51 |
=COUNTIF(in . Start with = and the function name, then open the bracket.=COUNTIF(B2:B7in . First, the range to look through: the Team column, B2 to B7. You can type it, or drag down the cells.=COUNTIF(B2:B7,"Finance"in . After a comma, the criteria: what to count. Text goes in "quotes". Capitals don't matter, so "finance" works too.=COUNTIF(B2:B7,"Finance")in . Close the bracket and press Enter. Amira, Cara and Finn are in Finance, so the answer is 3.
Worked example: counting numbers
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Hours | Finance staff | ||
| 2 | Amira | Finance | 12 | 30+ hours | ||
| 3 | Ben | Sales | 30 | Finance and 30+ | ||
| 4 | Cara | Finance | 45 | |||
| 5 | Dev | Support | 8 | |||
| 6 | Ewa | Sales | 32 | |||
| 7 | Finn | Finance | 51 |
=COUNTIF(C2:C7in . This time the range is the Hours column, C2 to C7.=COUNTIF(C2:C7,">=30")in . A comparison goes inside the quotes too.">=30"means 30 or more. Ben has exactly 30, and >= counts him, so the answer is 4.
Two conditions: COUNTIFS
The ranges in each pair must be the same size: B2:B7 with C2:C7.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Hours | Finance staff | ||
| 2 | Amira | Finance | 12 | 30+ hours | ||
| 3 | Ben | Sales | 30 | Finance and 30+ | ||
| 4 | Cara | Finance | 45 | |||
| 5 | Dev | Support | 8 | |||
| 6 | Ewa | Sales | 32 | |||
| 7 | Finn | Finance | 51 |
=COUNTIFS(B2:B7,"Finance"in . COUNTIFS takes pairs: a range, then what to look for in it. The first pair looks for Finance in the Team column.=COUNTIFS(B2:B7,"Finance",C2:C7,">=30")in . The second pair looks for 30 or more in the Hours column. A row is only counted if every pair matches: Cara and Finn, so 2.
What you'll practise
- In H2, count how many employees are in the Finance team.
- In H3, count how many employees have attendance below 90%.
- In H4, count the employees in Sales who are also in the North region.
- In H5, count employees with at least 50 training hours and attendance of at least 95%.