COUNTIFS counts how many rows meet all of your conditions. It is the counting version of SUMIFS.
Syntax
=COUNTIFS(range1, criteria1, range2, criteria2, ...)
Count with text criteria
To count tickets that are Open and assigned to Amit (status in B, owner in C):
=COUNTIFS(B2:B200, "Open", C2:C200, "Amit")
Count between two dates
=COUNTIFS(D2:D200, ">="&F1, D2:D200, "<="&G1)
F1 holds the start date and G1 the end date. Joining the operator and the cell with & is the key.
Count with wildcards
"Pro*" counts text starting with Pro."*delay*" counts text containing delay."?????" counts text with exactly five characters.
Count blanks and non-blanks
- Blank cells:
"="
- Not blank:
"<>"
Useful in MIS reports
COUNTIFS is ideal for tracking queue items: how many are open, how many are overdue, how many were closed this week. Combine it with a drop-down list and the report updates when you change the filter.
All ranges must be the same size, or you will get a #VALUE! error.
Worked example
Column A is Region, column B is Status. To count rows where the region is North and the status is Paid, use =COUNTIFS(A2:A100,"North",B2:B100,"Paid"). If North has 4 paid rows and 2 unpaid rows, the result is 4. Add a third pair for a date or amount condition.
Common mistakes
- Using ranges of different sizes. Every range must have the same number of rows.
- Expecting OR logic. COUNTIFS counts rows that meet all conditions (AND). For OR, add two COUNTIFS together.
- Putting a number condition without quotes, for example
>=500. Use ">=500".
Frequently asked questions
Can COUNTIFS do OR?
Not directly. Add two results: =COUNTIFS(A:A,"North")+COUNTIFS(A:A,"South").
How do I count between two dates?
Use two conditions on the same range: =COUNTIFS(C2:C100,">="&E1,C2:C100,"<="&E2).
How many conditions can I use?
Up to 127 range and condition pairs.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: COUNTIFS, COUNTIF, SUMIFS.
Start Excel practice → Case studies
