Confident Cells

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.

Your turn

In H2, count how many employees are in the Finance team.

ABCDEFGH
1EmployeeTeamRegionAttendanceTraining hours
2GraceSalesNorth97%64Staff in Finance
3TomFinanceSouth88%22Attendance below 90%4
4PriyaSalesEast99%81North region staff in Sales2
5MarekSupportNorth91%3550+ hours and 95%+ attendance6
6NiaFinanceEast95%58
7OllieSupportSouth84%17
8LucySalesSouth93%46
9HamzaFinanceNorth89%29
10RachelSupportEast98%73
11SunilSalesNorth86%31
12TanyaFinanceSouth96%52
13WillSupportNorth94%40
14ZoeSalesEast92%49
15BenFinanceNorth97%66
16EllaSupportSouth90%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

ABCDEF
1NameTeamHoursFinance staff
2AmiraFinance1230+ hours
3BenSales30Finance and 30+
4CaraFinance45
5DevSupport8
6EwaSales32
7FinnFinance51
  1. =COUNTIF( in . Start with = and the function name, then open the bracket.
  2. =COUNTIF(B2:B7 in . First, the range to look through: the Team column, B2 to B7. You can type it, or drag down the cells.
  3. =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.
  4. =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

ABCDEF
1NameTeamHoursFinance staff
2AmiraFinance1230+ hours
3BenSales30Finance and 30+
4CaraFinance45
5DevSupport8
6EwaSales32
7FinnFinance51
  1. =COUNTIF(C2:C7 in . This time the range is the Hours column, C2 to C7.
  2. =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.

ABCDEF
1NameTeamHoursFinance staff
2AmiraFinance1230+ hours
3BenSales30Finance and 30+
4CaraFinance45
5DevSupport8
6EwaSales32
7FinnFinance51
  1. =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.
  2. =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

  1. In H2, count how many employees are in the Finance team.
  2. In H3, count how many employees have attendance below 90%.
  3. In H4, count the employees in Sales who are also in the North region.
  4. In H5, count employees with at least 50 training hours and attendance of at least 95%.