COUNTIF and COUNTIFS: counting rows that match
COUNTIF counts how many cells in a range match a condition, such as how many people are in the Support team. COUNTIFS does the same with two or more conditions.
A worked example
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Team | Hours |
| 2 | Grace | Sales | 14 |
| 3 | Tom | Support | 9 |
| 4 | Priya | Support | 12 |
| 5 | Marek | Finance | 21 |
| 6 | Nia | Support | 11 |
| 7 | Ollie | Sales | 16 |
How many people are in Support? Go down the Team column and keep a tally: Tom is 1, Priya is 2, Nia is 3. The answer is 3.
The formula
=COUNTIF(B2:B7,"Support")
B2:B7is where to look."Support"is what to count. Upper and lower case don't matter.
Better still, type the team into a cell and point to it. Then one formula answers the question for any team:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hours | Team | People | |
| 2 | Grace | Sales | 14 | Support | 3 | |
| 3 | Tom | Support | 9 | |||
| 4 | Priya | Support | 12 | |||
| 5 | Marek | Finance | 21 | |||
| 6 | Nia | Support | 11 | |||
| 7 | Ollie | Sales | 16 |
Handy conditions
| Condition | Counts cells that are |
|---|---|
"Support" | exactly Support |
"<>Support" | anything except Support |
">12" | more than 12 |
">="&H1 | at least the number in H1 |
"*north*" | text containing "north" anywhere (* stands for any characters) |
"<>" | not blank |
COUNTIFS: more than one condition
How many people in Support have done at least 10 hours of training? Looking at the table, Tom has 9, Priya 12 and Nia 11, so the answer is 2. Add a second pair of range and condition:
=COUNTIFS(B2:B7,"Support",C2:C7,">=10")
A row is only counted when it matches every condition.
Common mistakes
A count of 0 when you compare with a cell
">=H1" inside quotes is just text, so Excel looks for the letters H1. Keep the comparison in quotes and join the cell on with &: ">="&H1.
Counting when you meant to add up
COUNTIF tells you how many rows match. To add up their hours or sales, use SUMIFS.
#VALUE! in COUNTIFS
Every range must cover the same rows, such as B2:B7 and C2:C7.
Labels that look the same but aren't
A space at the end ("Support ") stops a match. TRIM removes stray spaces.
Try it now
In F2, count how many people are in the team typed in E2. Point to E2 rather than typing Support.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Team | Hours | Team | People | |
| 2 | Grace | Sales | 14 | Support | ||
| 3 | Tom | Support | 9 | |||
| 4 | Priya | Support | 12 | |||
| 5 | Marek | Finance | 21 | |||
| 6 | Nia | Support | 11 | |||
| 7 | Ollie | Sales | 16 | |||
| 8 | Lucy | Support | 3 | |||
| 9 | Hamza | Finance | 8 | |||
| 10 | Rachel | Sales | 19 | |||
| 11 | Sunil | Support | 15 |
Where it works
COUNTIF works in every version of Excel, Google Sheets and LibreOffice Calc. COUNTIFS works in Excel 2007 onwards and the others.