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.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

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?

H2

2 How many orders had between 5 and 10 Units (inclusive)?

H2

3 How many orders were not from the East?

H2

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 pricing

Common COUNTIFS mistakes

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.

Keep practicing