COUNTIF function
COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp". 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
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.