The COUNTIFS function in Excel
COUNTIFS counts the rows where every condition you list is true, such as orders from the East region that were also laptops. It's COUNTIF with as many conditions as you need, and it's a staple of sales reports, HR headcounts and interview tasks that ask "how many X are also Y?".
Syntax
=COUNTIFS(criteria_range1, criteria1, ...)
| criteria_range1 | The first range to check, like B2:B9. |
|---|---|
| criteria1 | The condition for criteria_range1, like "East" or ">=350". |
| criteria_range2, criteria2, ... | Optional. More range and condition pairs. All ranges must be the same size, and a row is counted only when every condition is met. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In F2, use COUNTIFS to count employees who are in the Engineering department and work Remote.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Type | Remote engineers | |||||
| 2 | Full-time | ||||||
| 3 | Full-time | ||||||
| 4 | Contract | ||||||
| 5 | Contract | ||||||
| 6 | Full-time | ||||||
| 7 | Full-time | ||||||
| 8 | Full-time | ||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Tip: You need two range/condition pairs: one for the department column and one for the location column.
Examples
=COUNTIFS(B2:B9,"East",C2:C9,"Laptop")→ 2Counts orders that are both in the East region and for a laptop.
=COUNTIFS(B2:B9,"West",D2:D9,">=350")→ 2Mixes a text condition with a number comparison: West orders worth at least 350.
=COUNTIFS(D2:D9,">=300",D2:D9,"<=1000")→ 4Uses the same column twice to count amounts between 300 and 1,000, inclusive.
Example data
| 1 | Order | Region | Product | Amount |
|---|---|---|---|---|
| 2 | 1001 | East | Laptop | 1250 |
| 3 | 1002 | West | Monitor | 340 |
| 4 | 1003 | East | Monitor | 410 |
| 5 | 1004 | North | Laptop | 1180 |
| 6 | 1005 | West | Laptop | 990 |
| 7 | 1006 | East | Keyboard | 85 |
| 8 | 1007 | West | Monitor | 365 |
| 9 | 1008 | East | Laptop | 1320 |
Common mistakes
Ranges of different sizes
B2:B9withC2:C10returns #VALUE!. Every criteria range must cover exactly the same rows.Expecting OR logic
COUNTIFS only does AND. For East or West, add two formulas:
=COUNTIFS(B2:B9,"East")+COUNTIFS(B2:B9,"West").Comparing to a cell the wrong way
Write
">="&F2, not">=F2". Inside quotes, F2 is just text.Pairs out of order
Arguments go range, condition, range, condition. Swapping them makes Excel treat your condition as a range and return the wrong count or an error.
FAQ
What's the difference between COUNTIF and COUNTIFS?+
COUNTIF takes one condition. COUNTIFS takes one or more, and counts only rows that meet all of them. COUNTIFS with a single pair works just like COUNTIF.
How do I count between two dates with COUNTIFS?+
Use the date column twice: =COUNTIFS(A2:A50,">="&F1,A2:A50,"<="&F2), with the start date in F1 and end date in F2.
Can COUNTIFS use OR instead of AND?+
Not directly. Add separate COUNTIFS together, one for each alternative, making sure the options can't overlap.
Lesson
Master COUNTIFS step by step: COUNTIFS and SUMIFS
Count and sum with several conditions at once, and learn why SUMIFS puts the sum range first.