COUNTIF function
Quick answer
COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp".
- Syntax
=COUNTIF(range, criteria)- Most common use
=COUNTIF(B2:B8,"Open")returns 4 (Matching ignores case, so "open" counts too)
The condition can be an exact value, a comparison in quotes such as ">1000", a wildcard pattern, or a cell reference.
- Available in
- Every current version
- Returns
- The number of cells that meet the condition.
- Google Sheets
- Also available in Google Sheets.
Syntax
=COUNTIF(range, criteria)
| Argument | Required? | What it is |
|---|---|---|
range | Required | The cells to check. |
criteria | Required | The condition: a value like "Open", a comparison like ">=3", a pattern like "*login*", or a cell. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =COUNTIF(B2:B8,"Open") | 4 | Matching ignores case, so "open" counts too |
| G3 | =COUNTIF(C2:C8,">=3") | 4 | A comparison goes inside the quotes |
| G4 | =COUNTIF(D2:D8,"*login*") | 3 | Wildcards: * matches any characters around the word |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Ticket | Status | Days open | Subject | Check | Result | |
| T-101 | Open | 4 | Login issue | Open tickets | 4 | |
| T-102 | Closed | 1 | Billing question | Open 3+ days | 4 | |
| T-103 | Open | 7 | Login is slow | About login | 3 | |
| T-104 | Pending | 2 | Refund request | |||
| T-105 | Open | 1 | New user setup | |||
| T-106 | Closed | 5 | Password reset | |||
| T-107 | open | 3 | Login loop |
Practice COUNTIF
From lesson 7, COUNTIF & SUMIF. Graded instantly, then re-checked on changed data.
Count by region
G2, count how many orders came from the East region.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The count is 0 for a comparison | The operator must be inside quotes: ">=3", not >=3. To compare with a cell, join them: ">="&E2. |
| Text with extra spaces doesn't match | "Open " with a trailing space isn't "Open". Clean the data with TRIM. |
| Counts are off by a few | COUNTIF ignores case, so it can't tell "ABC" from "abc". Use SUMPRODUCT with EXACT if case matters. |
Tips
- Use "<>" to count non-empty cells and "<>Closed" to count everything except one value.
- For two or more conditions, switch to COUNTIFS rather than nesting COUNTIFs.
Questions
How do I count cells that contain specific text?
Use wildcards: =COUNTIF(D2:D8,"*login*") counts any cell containing "login".
How do I count cells greater than a value in another cell?
Join the operator and the cell: =COUNTIF(C2:C8,">"&F2).
Is COUNTIF case-sensitive?
No. "open", "Open" and "OPEN" all match.
Lessons that use COUNTIF
Answers that use COUNTIF
In other languages
COUNTIF is CONTAR.SI in Spanish Excel and ZÄHLENWENN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .