Excel track · Exercise 19 of 86 Paid

COUNTIFS: several conditions

Count the rows that satisfy two conditions at once instead of filtering the table first.

Advanced

Ticket list by region, status and unit count

Read-only data · screen 1 of 2 · answer in D9

In D9, count the North orders that are still open with one function taking two conditions.

	A	B	C	D
1	Order	Region	Status	Units
2	SO-1001	North	Open	5
3	SO-1002	South	Open	3
4	SO-1003	North	Closed	4
5	SO-1004	East	Open	2
6	SO-1005	North	Open	6
7	SO-1006	South	Closed	1
8	SO-1007	North	Open	8
9	North & open

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D9, count the open orders that came to more than 5 units.

	A	B	C	D
1	Order	Region	Status	Units
2	SO-1001	North	Open	5
3	SO-1002	South	Open	3
4	SO-1003	North	Closed	4
5	SO-1004	East	Open	2
6	SO-1005	North	Open	6
7	SO-1006	South	Closed	1
8	SO-1007	North	Open	8
9	Open & over 5 units

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

COUNTIFS counts the rows where every condition holds at the same time. Its arguments alternate range, condition, range, condition, and each range has to cover the same rows. COUNTIF does the same job for a single condition, which is why a second pair needs the other function.

Here B2:B8 must read North and C2:C8 must read Open, so rows 2, 6 and 8 qualify and the answer is 3. Asking COUNTIF for North alone returns 4, and the extra row is the closed North order in row 4.

The second screen swaps one text condition for a comparison, so D2:D8 has to come to more than 5 units. Row 2 holds exactly 5 and drops out, leaving 2. Comparisons travel inside quotes, and every condition you add can only narrow the count.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Worked example

On Ticket list by region, status and unit count, =COUNTIFS(B2:B8,"North",C2:C8,"Open") returns 3.

=COUNTIFS(B2:B8,"North",C2:C8,"Open")

Common errors

Error codes for COUNTIFS with two condition pairs, text and comparison criteria
Error	What it means and how to fix it
`#VALUE!`	One criteria range covers a different number of rows from the others, so the conditions stop lining up.
`Wrong total`	Two conditions that exclude each other, such as North and South on the same column, can never both hold for one row, and the count comes back as 0.
`One condition`	Dropping the second pair counts every North order, which is 4 rather than 3.

At work

Counting open tickets in one region, and counting orders above a credit limit before dispatch.