Excel guide · 2 min read
COUNTIFS with multiple criteria in Excel
COUNTIFS takes pairs of range and criteria and counts the rows where every pair matches: =COUNTIFS(B2:B9,"Open",C2:C9,"High") counts open, high-priority tickets. For OR logic (this or that), add two COUNTIFS together. All ranges must be the same size.
Sample data
Every formula on this page was calculated live against this sheet.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Agent | Status | Priority | Days open | Min days | |
| 2 | Jo | Open | High | 5 | 3 | |
| 3 | Sam | Closed | Low | 1 | ||
| 4 | Jo | Open | Low | 2 | ||
| 5 | Rita | Open | High | 8 | ||
| 6 | Sam | Open | Medium | 4 | ||
| 7 | Rita | Closed | High | 3 | ||
| 8 | Jo | Closed | Medium | 6 | ||
| 9 | Sam | Open | High | 1 |
Two text criteria (AND logic)
Each criteria range is checked row by row, and a row is counted only if it matches all of them. Text criteria go in quotes and are not case-sensitive.
Result: 3
Rows 2, 5 and 9 are both Open and High, so the count is 3.
Number comparisons and criteria from a cell
Put the operator inside quotes, like ">3". To compare against a cell, join the operator and the cell with &: ">"&F2. Then you can change the threshold in F2 without editing the formula.
Dates work the same way, for example ">="&DATE(2026,1,1).
Result: 3
Open tickets older than 3 days: 5, 8 and 4 days. The count is 3.
Between two values: same range twice
To count numbers in a band, list the same range twice with a lower and an upper limit. COUNTIFS treats them as two conditions that must both be true.
Result: 4
Tickets open 2 to 5 days: 5, 2, 4 and 3. The count is 4.
OR logic: add COUNTIFS together
COUNTIFS only does AND. For "open tickets that are High or Medium", write one COUNTIFS per option and add them. This works because a row cannot be both High and Medium, so nothing is counted twice.
Result: 4
3 open High tickets plus 1 open Medium ticket gives 4.
Wildcards and "not equal to"
An asterisk matches any characters and a question mark matches one, so "R*" means "starts with R". Use "<>Closed" for "anything except Closed", and "<>" alone for "not blank".
Result: 2
Rita has two High tickets, so names starting with R give a count of 2.
Which approach to use when
Use COUNTIFS whenever every condition must be true; it is fast, works in Excel 2007 and later, and is easy for others to read. Use added COUNTIFS for a short list of OR options on one column. If the OR crosses columns (for example "High priority or assigned to Jo"), adding COUNTIFS would count some rows twice; SUMPRODUCT handles that, as in =SUMPRODUCT(--(((C2:C9="High")+(A2:A9="Jo"))>0)).
If you need the matching rows themselves rather than a number, use FILTER in Excel 365 with the same conditions multiplied together, and wrap it in ROWS to count them. For a full breakdown by agent and status at once, a PivotTable (Insert > PivotTable) is quicker than writing a grid of COUNTIFS.
Common mistakes
Ranges of different sizes
B2:B9 with C2:C10 returns #VALUE!. Every range must have the same number of rows and columns.
Writing ">F2" in quotes
That looks for the text "F2". Put only the operator in quotes and join the cell: ">"&F2.
Expecting OR from one COUNTIFS
Two criteria on the same column, like "High" and "Medium", count rows that are both, which is zero. Add separate COUNTIFS instead.
FAQ
How many criteria can COUNTIFS handle?
Up to 127 range and criteria pairs. If you need that many, a PivotTable or FILTER is usually easier to manage.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition. COUNTIFS takes one or more, so it can do everything COUNTIF does. It works in Excel 2007 and later.
Can COUNTIFS count blank cells?
Yes. Use "" as the criteria to count empty cells, for example =COUNTIFS(B2:B9,"Open",D2:D9,""). Use "<>" to count non-empty cells.
Functions used
Keep learning
- How to use COUNTIF greater than in Excel
- SUMIFS with multiple criteria in Excel
- How to count cells with text in Excel
Updated 2026-10-31