COUNTIFS

What it does

COUNTIFS counts the rows where every condition is true. Each condition is a pair: a range and what it must match. All the ranges must be the same size.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

criteria_range

A range to check.

criteria

What that range must match: text in quotes, a comparison such as ">10", or a cell.

Example

Each order has a colour and a size. How many are red and large?

The formula in D2:

=COUNTIFS(A2:A6,"Red",B2:B6,"Large")

Result: 2

Rows 4 and 5 are both red and large, so the count is 2. Row 2 is red but small, and row 3 is large but blue.

Common mistakes

• Ranges of different sizes give a #VALUE! error.
• Using it for either/or. All the conditions must be true; for red or blue, add two COUNTIF results.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• COUNTIFS: several conditionsModule 3 · Counting
• Checking invoicesModule 14 · Combined skills

More from this module

• COUNT
• COUNTA
• COUNTBLANK
• COUNTIF

All functions