COUNTIF counts how many cells in a range meet one condition, such as matching a label or exceeding a number. It returns a single number, so you can quickly measure occurrences without sorting or filtering.
=COUNTIF(range, criteria)What does COUNTIF do?
COUNTIF is a statistical function that answers a simple but frequent question: how many cells meet this condition? You give it a range of cells and a criterion, and it returns a count. The criterion can be text, a number, a date, or an expression such as ">1000". It is one of the quickest ways to turn a list into a useful summary.
The function matters because counting by hand is slow and error-prone, especially when data changes. COUNTIF updates automatically when values change, so a dashboard or report stays correct without extra work. It also works well with other functions, letting you build summaries, validate data, and flag exceptions.
A typical situation: you have a sales table with regions and amounts, and your manager asks how many orders came from the North region or how many exceeded 1,000. Instead of filtering and counting rows, you write one COUNTIF formula and get the answer instantly. The same pattern applies to attendance sheets, inventory lists, survey responses, and expense reports.
When to use COUNTIF
- Count how many times a product name appears in an order list.
- Count sales above a target amount.
- Count dates within a specific month or after a deadline.
- Count blank or non-blank cells in a checklist.
- Count entries that begin with or contain certain text.
- Count duplicate values before removing duplicates.
COUNTIF syntax
=COUNTIF(range, criteria)| Argument | Required? | What to enter |
|---|---|---|
range | Required | The cells to inspect. It can be a column, a row, or a block such as B2:B10. COUNTIF looks at each cell in this range. |
criteria | Required | The condition each cell must meet. Use text in quotes, a number, a date, or an operator expression such as ">=500". Wildcards * and ? work with text. |
What it returns: A whole number representing how many cells in the range meet the criteria. If no cells match, it returns 0.
COUNTIF examples
1 Count exact text matches
The goal is to count how many orders came from the North region. The formula =COUNTIF(A2:A6,"North") checks each cell in A2:A6 and counts every cell equal to North. It returns 2 because rows 2 and 5 contain North. The remaining regions are ignored.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 2 | ||
| 3 | South | 850 | |||
| 4 | East | 1500 | |||
| 5 | North | 950 | |||
| 6 | West | 1100 | |||
| 7 |
2 Count numbers above a threshold
Here you want to know how many sales amounts are greater than 1,000. The formula =COUNTIF(B2:B6,">1000") compares each value in B2:B6 with 1,000. It counts 1200, 1500, and 1100, so the result is 3. The value 1000 itself would not be counted because the operator is strictly greater than.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 3 | ||
| 3 | South | 850 | |||
| 4 | East | 1500 | |||
| 5 | North | 950 | |||
| 6 | West | 1100 | |||
| 7 |
3 Count text that contains a word
You need to count how many product descriptions mention Pro. The formula =COUNTIF(B2:B7,"*Pro*") uses wildcards so any cell containing Pro anywhere matches. It counts Pro Stand, Pro Case, and Pro Mount, returning 3. Basic Stand and Basic Case do not match because they lack the word Pro.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Description | Price | |||
| 2 | P1 | Pro Stand | 45 | 3 | ||
| 3 | P2 | Basic Stand | 30 | |||
| 4 | P3 | Pro Case | 25 | |||
| 5 | P4 | Basic Case | 20 | |||
| 6 | P5 | Pro Mount | 60 | |||
| 7 | P6 | Cable | 10 | |||
| 8 |
4 Count blank cells in a checklist
The goal is to count how many checklist items have no completion date. The formula =COUNTIF(C2:C7,"") counts empty cells in C2:C7. Rows 3, 5, and 7 are blank, so it returns 3. Non-blank dates are ignored.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Step | Owner | Completed | |||
| 2 | Draft | Ana | 2024-04-01 | 3 | ||
| 3 | Review | Ben | ||||
| 4 | Approve | Cara | 2024-04-03 | |||
| 5 | Publish | Dan | ||||
| 6 | Archive | Eve | 2024-04-05 | |||
| 7 | Notify | Finn | ||||
| 8 |
Common COUNTIF errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The criteria is a text comparison without quotes, such as =COUNTIF(A2:A10,>5). | Wrap operator expressions in quotes: =COUNTIF(A2:A10,">5"). |
| Wrong count (0 or too low) | The range and criteria refer to different columns, or the text has extra spaces. | Check that the range covers the data you mean and use TRIM or clean the source text if needed. |
| Wildcards not working | The criteria is a number or the wildcard is placed outside quotes. | Use wildcards only with text criteria and keep them inside quotes, for example "*Pro*". |
| #NAME? | The function name is misspelled, such as COUNTIFS used with one range or COUNTIF typed incorrectly. | Type =COUNTIF( and let Excel suggest the function, or correct the spelling. |
Tips for using COUNTIF
- Use
COUNTIFfor one condition; switch toCOUNTIFSwhen you need two or more conditions. - Put operator criteria in quotes, such as
">=100", but plain numbers can be entered without quotes. - Use
*to match any number of characters and?to match a single character in text criteria. - To count non-blank cells, use
""as the criteria. - Reference a cell in the criteria to make the count dynamic, for example
=COUNTIF(B2:B10,">"&D2). - Remember that COUNTIF is not case-sensitive, so North and north are counted together.
Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet one condition. COUNTIFS counts cells that meet two or more conditions across multiple ranges.
Can COUNTIF count cells that contain specific text?
Yes. Use wildcards in the criteria, such as "*Pro*" to count any cell containing Pro.
Does COUNTIF work with dates?
Yes. Use a date or a comparison in quotes, such as ">2024-03-15", and make sure the cells contain real dates or date text in a consistent format.
Why does COUNTIF return 0 when I can see matching values?
Common causes are extra spaces, numbers stored as text, or a range that does not include the rows you expect. Clean the data or adjust the range.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. COUNTIF reference