Excel function guide · Conditional sums & counts
COUNTIFS function in Excel
COUNTIFS counts the rows that meet every condition you give it, such as tickets that are open and belong to the Tech team. Each condition is a pair: a range to test and the criterion to test it against.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | Required? | What it means |
|---|---|---|
| criteria_range1 | Required | The first range to test. |
| criteria1 | Required | Its condition: a value, a cell, a comparison in quotes (">5") or a wildcard pattern. |
| criteria_range2, criteria2, … | Optional | More pairs, all the same size as the first range. A row counts only if it meets them all. |
COUNTIFS examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Team | Priority | Status | Days open |
| 2 | Billing | High | Open | 3 |
| 3 | Tech | High | Closed | 1 |
| 4 | Tech | Low | Open | 9 |
| 5 | Billing | Low | Open | 12 |
| 6 | Tech | High | Open | 6 |
| 7 | Billing | High | Closed | 2 |
1. Two text conditions
=COUNTIFS(A2:A7, "Tech", C2:C7, "Open")
Result: 2
Tech tickets that are still open: rows 4 and 6.
2. Not equal to
=COUNTIFS(B2:B7, "High", C2:C7, "<>Closed")
Result: 2
"<>Closed" counts every status except Closed.
3. Between two numbers
=COUNTIFS(D2:D7, ">=3", D2:D7, "<=9")
Result: 3
Use the same range twice for a lower and an upper limit: 3, 9 and 6 days.
Try it now
Practise COUNTIFS in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F3, count the Open tickets for the team in F2.
Common COUNTIFS mistakes
Expecting OR logic
Conditions are combined with AND. To count Billing or Tech, add two COUNTIFS: =COUNTIFS(A2:A7,"Billing")+COUNTIFS(A2:A7,"Tech").
Ranges of different sizes
Every criteria range must have the same number of rows and columns, or COUNTIFS returns #VALUE!.
Operators outside the quotes
Write ">5" with the operator inside the quotes. To use a cell: ">"&F2.
Trailing spaces in the data
"Open " with a space isn't "Open". If counts look too low, check the data with TRIM. Case doesn't matter.
Practise this in Excel Quest
SUMIFS & COUNTIFS Members
Course 5 · Conditional Aggregation · 12 min
Answer business questions that need two, three or a date range of conditions at once.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.