How to use COUNTIFS to count rows that meet two conditions
Counting is easy until you need more than one rule. COUNTIFS takes as many rules as you like and counts only the rows where all of them are true.
The formula
=COUNTIFS(A:A,"North",B:B,"Open") in column D
What each part does
A:A,"North"- The first rule: the region has to be North.
B:B,"Open"- The second rule: the status has to be Open.
COUNTIFS- Counts only the rows where every rule is true at once.
How it works, and what to watch for
The general idea: rules come in pairs. First the column to check, then what to look for. Add as many pairs as you need.
Every rule has to be true at the same time for a row to be counted. If you want rows that match one rule or another, that is a different job, and it is usually done by adding two COUNTIFS together.
All the ranges must cover the same rows. If one stops early, the count is wrong rather than an error, which makes it hard to spot. Using whole columns such as A:A avoids the problem.
COUNTIF, without the S, is the same thing with a single rule.
Questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition. COUNTIFS takes several and counts only the rows where all of them hold.
Can I count rows that match either condition?
Not directly. Add two counts together and subtract the overlap: =COUNTIFS(...)+COUNTIFS(...)-COUNTIFS(both).
Does it ignore capital letters?
Yes. COUNTIFS treats North and NORTH as the same. If case matters, EXACT is the function to use.
The example sheet
| Region |
Status |
North, open |
| North |
Open |
|
| South |
Open |
|
| North |
Closed |
|
| North |
Open |
|
| South |
Closed |
|
If this helped, these are close by
More on COUNTIFS, and every walkthrough that uses it.
