COUNTIF in Excel: Count Text, Contains and Blanks
· 6 min read
COUNTIF counts the cells in a range that meet one condition. Every COUNTIF Excel formula has two parts: where to look, and what to count. Text goes in double quotes, and wildcards let you count cells that contain a word:
=COUNTIF(range, criteria)For example, =COUNTIF(C2:C11, "West") counts the cells in C2:C11 that say West. It is not case sensitive, so "west" and "WEST" are counted too. If you need two or more conditions at once, you want COUNTIFS, which we cover in COUNTIF with multiple criteria.
The example table
Ten orders from a supplies company. Some have not shipped yet, so column D is empty for them. F2 and F3 hold a region and a word to count, column E has the duplicate check from later in this guide, and column H has the other COUNTIF formulas.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Product | Region | Shipped on | Copies of order | Input | Question | Result |
| 2 | 4001 | Paper towels | North | 2025-02-03 | 1 | West | Region is West | 4 |
| 3 | 4002 | Glass cleaner | West | 1 | towels | Region in F2 | 4 | |
| 4 | 4003 | Paper cups | East | 2025-02-04 | 1 | Region is not West | 6 | |
| 5 | 4004 | Floor cleaner | West | 2025-02-05 | 1 | Product contains cleaner | 3 | |
| 6 | 4005 | Copy paper | South | 1 | Product contains F3 | 2 | ||
| 7 | 4006 | Kitchen towels | North | 2025-02-06 | 1 | Not shipped yet | 4 | |
| 8 | 4007 | Desk cleaner wipes | West | 2025-02-06 | 1 | Shipped on or after 5 Feb | 4 | |
| 9 | 4008 | Coffee filters | East | 1 | AI's formula: West orders | 0 | ||
| 10 | 4009 | Paper plates | West | 2025-02-07 | 1 | Fixed | 4 | |
| 11 | 4010 | Bin bags | North | 1 |
COUNTIF text: count an exact word
=COUNTIF(C2:C11, "West")Result: 4 (orders 4002, 4004, 4007 and 4009).
An exact text criterion has to match the whole cell. "West" does not match "North-West" or "West " with a trailing space. To count from a cell instead of typing the word, drop the quotes and point at it. If F2 contains West:
=COUNTIF(C2:C11, F2)Result: 4. This is how you build a small summary table: list the regions in F2:F5, write the formula once and fill it down with the range locked as $C$2:$C$11.
Not equal to
Put <> in front of the value:
=COUNTIF(C2:C11, "<>West")Result: 6, every order outside the West.
COUNTIF contains: wildcards
To count cells that contain a word anywhere, wrap it in asterisks. * means "any number of characters, including none".
=COUNTIF(B2:B11, "*cleaner*")Result: 3: Glass cleaner, Floor cleaner and Desk cleaner wipes.
Where you put the asterisks changes the meaning:
| Criteria | Meaning | Result |
|---|---|---|
"paper*" | Starts with "paper" | 3 |
"*paper*" | Contains "paper" anywhere | 4 |
"*towels" | Ends with "towels" | 2 |
"????? cleaner" | Exactly five characters, then " cleaner" | 2 |
"*paper*" picks up Copy paper as well as the three products that start with Paper. The ? wildcard stands for exactly one character, so "????? cleaner" matches Glass cleaner and Floor cleaner, but not Desk cleaner wipes.
Contains a word typed in a cell
Join the asterisks to the cell with &. If F3 contains towels:
=COUNTIF(B2:B11, "*"&F3&"*")Result: 2. Quoting the cell, as in "*F3*", would search for the letters F3 instead.
Counting a real asterisk or question mark
To count a literal * or ?, put a tilde in front of it: "*~?*" counts cells that contain a question mark.
COUNTIF blank cells
Use an empty pair of quotes to count empty cells:
=COUNTIF(D2:D11, "")Result: 4: the orders that have not shipped. COUNTBLANK gives the same answer and is easier to read:
=COUNTBLANK(D2:D11)Both treat a cell that contains a formula returning "" as blank. Neither treats a cell with a single space as blank, because a space is a character. If a count of blanks looks too low, check for cells that only look empty.
To count the cells that are not blank, use "<>" or COUNTA. Our COUNTIF with multiple criteria guide covers that, along with greater than, between two numbers and OR logic.
COUNTIF with numbers and dates
The same pattern works for numbers, with the operator inside the quotes. To count orders shipped on or after 5 February 2025:
=COUNTIF(D2:D11, ">="&DATE(2025,2,5))Result: 4 (orders 4004, 4006, 4007 and 4009). Using DATE avoids problems with how dates are typed in different regions. Wildcards do not work on numbers or dates, only on text.
COUNTIF to find duplicates
COUNTIF can also tell you how often each value appears in its own column. In E2, next to the first order:
=COUNTIF($A$2:$A$11, A2)Fill it down. Every order number in our table is unique, so each row shows 1. If an order had been pasted in twice, both copies would show 2. To get a simple flag instead, compare the count with 1: =COUNTIF($A$2:$A$11, A2)>1 returns TRUE for duplicates. The range must be locked with $ signs, or it shrinks as you fill down and the later rows miss earlier copies. Our absolute reference guide explains why.
Common mistakes
Using COUNT instead of COUNTIF or COUNTA. AI assistants sometimes reach for COUNT when asked "how many orders are in the West". COUNT only counts cells that contain numbers, so =COUNT(C2:C11) on the region column returns 0. On a column of IDs it counts everything, ignoring the condition. If the formula has no criteria in it at all, it is not answering a "how many where" question.
Wildcards where you wanted an exact match. An assistant may "help" by writing "*West*" to be safe. That also counts "North-West" or "Westfield Ltd" if they exist in the column. Use wildcards only when you mean "contains".
Quoting a cell reference. "F2" and ">=F2" look for the text F2. Write F2 on its own, or ">="&F2 with an operator.
Invisible differences in the data. Trailing spaces and different spellings ("Cancelled" vs "canceled") split one category into two. Case does not matter to COUNTIF, but spaces and spelling do.
Counting when you meant to add. COUNTIF tells you how many rows match, not how much they are worth. To add the amounts on matching rows, use SUMIF or SUMIFS: see SUMIF with multiple criteria.
FAQ
How do I use COUNTIF with text?
Put the text in double quotes: =COUNTIF(C2:C11, "West"). It matches the whole cell and ignores upper and lower case.
How do I count cells that contain a specific word?
Use asterisks around the word: =COUNTIF(B2:B11, "*cleaner*"). For a word typed in a cell, use "*"&F3&"*".
How do I count blank cells in Excel?
Use =COUNTBLANK(D2:D11) or =COUNTIF(D2:D11, ""). Cells containing only a space are not counted as blank.
Is COUNTIF case sensitive?
No. "West", "west" and "WEST" all match the same cells. If you need a case-sensitive count, you need a different approach, such as SUMPRODUCT with EXACT.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition. COUNTIFS takes one or more, and a row is counted only when all of them are true.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Count the completed orders” →