Confident Cells

COUNTIF and COUNTIFS: counting rows that match

20 November 2026 · 2 min read

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

ABC
1EmployeeTeamHours
2GraceSales14
3TomSupport9
4PriyaSupport12
5MarekFinance21
6NiaSupport11
7OllieSales16

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")

Better still, type the team into a cell and point to it. Then one formula answers the question for any team:

ABCDEF
1EmployeeTeamHoursTeamPeople
2GraceSales14Support3
3TomSupport9
4PriyaSupport12
5MarekFinance21
6NiaSupport11
7OllieSales16
F2 holds =COUNTIF(B2:B7,E2).

Handy conditions

ConditionCounts cells that are
"Support"exactly Support
"<>Support"anything except Support
">12"more than 12
">="&H1at 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

Your turn

In F2, count how many people are in the team typed in E2. Point to E2 rather than typing Support.

ABCDEF
1EmployeeTeamHoursTeamPeople
2GraceSales14Support
3TomSupport9
4PriyaSupport12
5MarekFinance21
6NiaSupport11
7OllieSales16
8LucySupport3
9HamzaFinance8
10RachelSales19
11SunilSupport15

Where it works

COUNTIF works in every version of Excel, Google Sheets and LibreOffice Calc. COUNTIFS works in Excel 2007 onwards and the others.