Excel practice › Excel functions › COUNTIFS
COUNTIFS in Excel: count the rows that meet every condition
COUNTIFS counts the rows that meet every condition you give it. With departments in B2:B9 and offices in C2:C9, the number of Sales people in Austin is =COUNTIFS(B2:B9,"Sales",C2:C9,"Austin").
Conditions come in pairs: a range, then what to look for in it. A row is counted only when all the pairs are true for it.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
criteria_range1- The first range to test.
criteria1- The condition for that range:
"Sales",">=5", a cell likeF2, or an operator joined to a cell,">="&F2. criteria_range2, criteria2- More pairs, optional, up to 127 pairs in all. Each extra range needs the same number of rows and columns as
criteria_range1. The ranges don't have to be next to each other.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | Dept | Office | Count | |
| 2 | A. Rivera | Sales | Austin | 2 | Sales | Austin | 3 | |
| 3 | B. Chen | Sales | Denver | 6 | ||||
| 4 | C. Okafor | Finance | Austin | 4 | ||||
| 5 | D. Miller | Sales | Austin | 7 | ||||
| 6 | E. Novak | Support | Denver | 1 | ||||
| 7 | F. Haddad | Finance | Denver | 9 | ||||
| 8 | G. Brooks | Sales | Austin | 3 | ||||
| 9 | H. Tanaka | Support | Austin | 5 |
G2 with "Denver" and the count is 1.How Excel reads it
- It takes the first cell of every range and checks each one against its own condition.
- If all of them pass, the count goes up by 1.
- It does the same for the second cells, the third, and so on down the ranges.
So the ranges are read side by side, row by row. That is why they all need the same size.
Between two numbers
A range like "from 3 to 6" is two conditions on the same column: at least 3, and at most 6. The column appears twice, once for each limit.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | 3 to 6 yrs | |
| 2 | A. Rivera | Sales | Austin | 2 | 4 | |
| 3 | B. Chen | Sales | Denver | 6 | ||
| 4 | C. Okafor | Finance | Austin | 4 | ||
| 5 | D. Miller | Sales | Austin | 7 | ||
| 6 | E. Novak | Support | Denver | 1 | ||
| 7 | F. Haddad | Finance | Denver | 9 | ||
| 8 | G. Brooks | Sales | Austin | 3 | ||
| 9 | H. Tanaka | Support | Austin | 5 |
The mistake: AND when you meant OR
COUNTIFS counts a row only when all its conditions are true together. Ask for two different values of the same column and no row can ever pass. The result is 0, with no error to warn you:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | Sales or Finance | |
| 2 | A. Rivera | Sales | Austin | 2 | 0 | |
| 3 | B. Chen | Sales | Denver | 6 | ||
| 4 | C. Okafor | Finance | Austin | 4 | ||
| 5 | D. Miller | Sales | Austin | 7 | ||
| 6 | E. Novak | Support | Denver | 1 | ||
| 7 | F. Haddad | Finance | Denver | 9 | ||
| 8 | G. Brooks | Sales | Austin | 3 | ||
| 9 | H. Tanaka | Support | Austin | 5 |
=COUNTIFS(B2:B9,"Sales")+COUNTIFS(B2:B9,"Finance") gives 6.Two more things to check when a count looks wrong. Every range needs the same number of rows and columns as the first one. And a cell reference never goes inside the quotes: ">="&F2 compares with the value of F2, while ">=F2" compares with the text F2 and counts nothing.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
F2 holds a department. In G2, count the people in that department with 5 or more years at the company.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | Dept | 5+ years | |
| 2 | A. Rivera | Sales | Austin | 2 | Sales | ||
| 3 | B. Chen | Sales | Denver | 6 | |||
| 4 | C. Okafor | Finance | Austin | 4 | |||
| 5 | D. Miller | Sales | Austin | 7 | |||
| 6 | E. Novak | Support | Denver | 1 | |||
| 7 | F. Haddad | Finance | Denver | 9 | |||
| 8 | G. Brooks | Sales | Austin | 3 | |||
| 9 | H. Tanaka | Support | Austin | 5 |
Show the answer
=COUNTIFS(B2:B9,F2,D2:D9,">=5") gives 2.
Two pairs: the department equals F2, and the years are at least 5. Chen (6) and Miller (7) are the two Sales people who qualify.
F2 and G2 hold a minimum and a maximum number of years. In H2, count the people whose years are between the two, both included.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | Min | Max | Count | |
| 2 | A. Rivera | Sales | Austin | 2 | 2 | 5 | ||
| 3 | B. Chen | Sales | Denver | 6 | ||||
| 4 | C. Okafor | Finance | Austin | 4 | ||||
| 5 | D. Miller | Sales | Austin | 7 | ||||
| 6 | E. Novak | Support | Denver | 1 | ||||
| 7 | F. Haddad | Finance | Denver | 9 | ||||
| 8 | G. Brooks | Sales | Austin | 3 | ||||
| 9 | H. Tanaka | Support | Austin | 5 |
Show the answer
=COUNTIFS(D2:D9,">="&F2,D2:D9,"<="&G2) gives 4.
Column D is tested twice: at least F2, and at most G2. Each operator is in quotes and joined to its cell with &. Rivera (2), Okafor (4), Brooks (3) and Tanaka (5) are in range.
F2 holds an office and G2 a number of years. In H2, count the people in that office who are not in Sales and have more than G2 years.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Office | Years | Office | Over yrs | Count | |
| 2 | A. Rivera | Sales | Austin | 2 | Austin | 3 | ||
| 3 | B. Chen | Sales | Denver | 6 | ||||
| 4 | C. Okafor | Finance | Austin | 4 | ||||
| 5 | D. Miller | Sales | Austin | 7 | ||||
| 6 | E. Novak | Support | Denver | 1 | ||||
| 7 | F. Haddad | Finance | Denver | 9 | ||||
| 8 | G. Brooks | Sales | Austin | 3 | ||||
| 9 | H. Tanaka | Support | Austin | 5 |
Show the answer
=COUNTIFS(C2:C9,F2,B2:B9,"<>Sales",D2:D9,">"&G2) gives 2.
<> means "not equal to", and it goes inside the quotes together with the text. Okafor (4 years) and Tanaka (5) are in Austin, outside Sales, with more than 3 years.
Questions people ask
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one range and one condition. COUNTIFS takes one or more pairs and counts the rows that meet all of them. With a single pair they agree: =COUNTIFS(B2:B9,"Sales") and =COUNTIF(B2:B9,"Sales") both give 4 on the list above.
Does COUNTIFS use AND or OR?
AND. A row is counted only when every condition is true for it. For OR, add separate counts: =COUNTIFS(B2:B9,"Sales")+COUNTIFS(B2:B9,"Finance"). If a row could meet both conditions it would be counted twice, so this works cleanly when the two can't overlap, like two values of one column.
How do I count between two dates with COUNTIFS?
Use the date column twice: =COUNTIFS(A2:A9,">="&F2,A2:A9,"<="&G2), where F2 and G2 hold the first and the last date. The SUMIFS page has the same pattern running on a list of dated orders.
Why does COUNTIFS return 0?
Either no row meets all the conditions at once (the usual case is two values asked of the same column), or one condition never matches: a cell reference typed inside the quotes, or a text that differs from the data by a space. The COUNTIF page shows the space problem live.