COUNTIFS in Excel: Count With Multiple Conditions
How COUNTIFS counts rows that meet several conditions at once: text and number criteria, date ranges, between two values, and how to do OR logic.
By Ayoub Farid · · 1 min read
COUNTIFS counts rows where all your conditions are true. "How many Coffee orders over $3?" "How many tickets were opened in March and are still open?" It's COUNTIF with more than one condition.
Syntax
=COUNTIFS(range1, criteria1, range2, criteria2, ...)Each range must be the same size.
Example
| A | B | C | |
|---|---|---|---|
| 1 | Item | Category | Price |
| 2 | Espresso | Coffee | 2.50 |
| 3 | Latte | Coffee | 3.50 |
| 4 | Mocha | Coffee | 4.00 |
| 5 | Muffin | Food | 3.00 |
Coffee items over $3:
=COUNTIFS(B2:B5,"Coffee",C2:C5,">3") → 2Between two numbers
Use the same range twice:
=COUNTIFS(C2:C100,">=10",C2:C100,"<=20")Between two dates
With start date in F1 and end date in F2:
=COUNTIFS(D2:D500,">="&F1,D2:D500,"<="&F2)The & joins the operator to the cell. This is the standard pattern for monthly counts.
OR logic
COUNTIFS is AND-only. For OR (Coffee or Tea), add two COUNTIFs:
=COUNTIF(B2:B100,"Coffee")+COUNTIF(B2:B100,"Tea")Or in one formula with an array constant:
=SUM(COUNTIFS(B2:B100,{"Coffee","Tea"}))Build a summary table
List categories in F2:F4 and months in G1:I1, then in G2:
=COUNTIFS($B$2:$B$500,$F2,$D$2:$D$500,G$1)The $ signs let you fill it right and down. That's mixed references at work.
Common mistakes
- Ranges of different sizes give
#VALUE!. - Dates typed as text in criteria: use
">="&DATE(2026,3,1)or a date cell. - Expecting OR behaviour: COUNTIFS always means AND.
Frequently asked questions
How do I count with two conditions in Excel?
=COUNTIFS(range1,criteria1,range2,criteria2), e.g. =COUNTIFS(B:B,"Coffee",C:C,">3").
Can COUNTIFS do OR conditions?
Not directly. Add separate COUNTIFS together, or SUM an array of criteria.
How do I count dates in a month with COUNTIFS?
Use two conditions: on or after the first day and on or before the last day of the month.
Is COUNTIFS available in Google Sheets?
Yes, with identical syntax.