# COUNTIF function in Excel

> COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp".

Source: https://spreadsheetpractice.com/functions/countif · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Available in:** Every current version
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The number of cells that meet the condition.
- **Google Sheets:** Also available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Ticket | Status | Days open | Subject |  | Check | Result |
| 2 | T-101 | Open | 4 | Login issue |  | Open tickets | 4 |
| 3 | T-102 | Closed | 1 | Billing question |  | Open 3+ days | 4 |
| 4 | T-103 | Open | 7 | Login is slow |  | About login | 3 |
| 5 | T-104 | Pending | 2 | Refund request |  |  |  |
| 6 | T-105 | Open | 1 | New user setup |  |  |  |
| 7 | T-106 | Closed | 5 | Password reset |  |  |  |
| 8 | T-107 | open | 3 | Login loop |  |  |  |

## 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.

## Related functions

- [COUNTIFS](https://spreadsheetpractice.com/functions/countifs): COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.
- [SUMIF](https://spreadsheetpractice.com/functions/sumif): SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500.
- [COUNTA](https://spreadsheetpractice.com/functions/counta): COUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text.
- Comparison: [COUNTIF vs COUNTIFS](https://spreadsheetpractice.com/functions/compare/countif-vs-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.

## Practice

A graded COUNTIF exercise ("Count by region") is on [the COUNTIF page](https://spreadsheetpractice.com/functions/countif). It is checked instantly, then re-checked on changed data.

Lessons that use COUNTIF:

- [Lesson 7: COUNTIF & SUMIF](https://spreadsheetpractice.com/learn/countif-sumif)
- [Lesson 9: Conditional formatting with formulas](https://spreadsheetpractice.com/learn/conditional-formatting-formulas)
- [Lesson 43: Duplicates & reconciliation](https://spreadsheetpractice.com/learn/duplicates-reconciliation-checks) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
