Excel COUNTIFS function: count with two or more conditions
COUNTIFS counts rows that pass every condition you give it. =COUNTIFS(E2:E7,"East",B2:B7,">1000") counts reps in the East who sold over 1,000. Each condition is a pair: a range, then what to look for.
The examples use the same sales table as the practice box below.
| A Rep | B Sales | E Region | H Hired | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | East | 3/12/2018 |
| 3 | Luis | 830 | West | 7/1/2021 |
| 4 | Priya | 2,100 | East | 11/20/2016 |
| 5 | Marcus | 475 | North | 2/15/2023 |
| 6 | Jen | 990 | West | 9/3/2019 |
| 7 | Omar | 1,600 | East | 1/27/2020 |
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Examples
| Formula | Result | Counts |
|---|---|---|
=COUNTIFS(E2:E7,"East",B2:B7,">1000") | 3 | East reps over 1,000 |
=COUNTIFS(E2:E7,"West",B2:B7,"<1000") | 2 | West reps under 1,000 |
=COUNTIFS(B2:B7,">=800",B2:B7,"<=1300") | 3 | Sales between 800 and 1,300 |
Counting a range of numbers
Use the same column twice, once for the low end and once for the high end, as in the third example. This is a favorite test question because "800-1300" doesn't work.
Counting between two dates
=COUNTIFS(H2:H7,">="&DATE(2019,1,1),H2:H7,"<"&DATE(2022,1,1)) counts reps hired from 2019 through 2021. It returns 3.
OR logic
COUNTIFS is always AND. To count East or West, add two counts: =COUNTIF(E2:E7,"East")+COUNTIF(E2:E7,"West") returns 5.
Common mistakes
- Ranges of different sizes, which gives #VALUE!.
- Forgetting quotes around comparisons like
">1000". - Expecting OR behavior from one COUNTIFS.
Try it: East reps over 1000
Count the reps who are in the East region and sold more than 1000. Put the count in B8.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
How many conditions can COUNTIFS take?
Up to 127 range and condition pairs. Most real formulas use two or three.
Can COUNTIFS use wildcards?
Yes. =COUNTIFS(A2:A7,"J*",E2:E7,"West") counts West reps whose names start with J. See wildcards.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How to use SUMIFS in Excel to add values that meet several conditions, how its argument order differs from SUMIF, and a live exercise.
How to use the * and ? wildcards in COUNTIF, SUMIF, VLOOKUP and MATCH, how to search for a real asterisk, where wildcards don't work, and a live exercise.