The COUNTIF function in Excel
COUNTIF counts the cells in a range that meet one condition: how many tickets are open, how many orders are over $500, how many SKUs start with a given prefix. It's the go-to function for quick tallies in dashboards and one of the most common questions in Excel assessments.
Syntax
=COUNTIF(range, criteria)
| range | The cells to check, like C2:C8. |
|---|---|
| criteria | The condition. Use "Open" for exact text, ">24" for comparisons, "BUG-*" for wildcards, or join an operator to a cell with ">"&F2. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In F2, use COUNTIF to count how many products have fewer units in stock than the low-stock limit in E2. Refer to E2 so the count updates if the limit changes.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Units | Low-stock items | |||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 |
Tip: Put the operator in quotes and join it to E2 with the & sign.
Examples
=COUNTIF(C2:C8,"Open")→ 4Counts tickets whose status is Open. Text matching ignores upper and lower case.
=COUNTIF(D2:D8,">24")→ 3Counts tickets open for more than 24 hours. The operator goes inside the quotes with the number.
=COUNTIF(A2:A8,"BUG-*")→ 3The * wildcard matches any characters, so this counts every ticket ID that starts with BUG-.
Example data
| 1 | Ticket | Type | Status | Hours open |
|---|---|---|---|---|
| 2 | BUG-101 | Bug | Open | 30 |
| 3 | REQ-102 | Request | Closed | 4 |
| 4 | BUG-103 | Bug | Open | 52 |
| 5 | REQ-104 | Request | Open | 8 |
| 6 | BUG-105 | Bug | Closed | 12 |
| 7 | INC-106 | Incident | Open | 2 |
| 8 | REQ-107 | Request | Closed | 26 |
Common mistakes
Operator outside the quotes
=COUNTIF(D2:D8,>24)is a syntax error. The whole condition is text:">24".Cell reference inside the quotes
">F2"looks for the literal text F2. Join the operator to the cell instead:">"&F2.Hidden spaces stop matches
"Open " with a trailing space won't match "Open". Clean the column with TRIM, or use a wildcard like
"Open*".Trying to add a second condition
COUNTIF only takes one condition. For "Open and over 24 hours", switch to COUNTIFS.
FAQ
How do I use COUNTIF with multiple criteria?+
Use COUNTIFS, which takes range/criteria pairs and counts rows where all conditions are true. For either/or logic, add two COUNTIFs together.
Is COUNTIF case-sensitive?+
No. "open", "Open" and "OPEN" all match the same cells.
How do I count cells that contain certain text?+
Wrap the text in wildcards: =COUNTIF(A2:A50,"*urgent*") counts any cell with "urgent" anywhere in it.
Lesson
Master COUNTIF step by step: COUNTIF
Count the cells that match a condition, from exact words to number checks like ">=8".