To count cells with text in Excel, use =COUNTIF(A2:A10,"*"). The asterisk is a wildcard that matches any text, so numbers and empty cells are ignored. To count cells containing a particular word, such as Shipped, put that word in quote marks instead, or use "*word*" to match it anywhere in the cell.
Counting text sounds trivial until you try it. COUNT ignores words entirely, COUNTA counts everything including numbers, and neither tells you how many orders are marked Shipped. The function you want is COUNTIF with a wildcard, and once you see how it works you can count any label in any list.
Counting every cell that contains text
- Click an empty cell where you want the answer.
- Type =COUNTIF(A2:A10,"*"), changing A2:A10 to your range.
- Press Enter. Excel counts every cell in the range that holds text.
Microsoft's own example uses =COUNTIF(A2:A5,"*") to count the cells containing any text. The asterisk is a wildcard meaning any characters at all, so a cell with Pending counts and a cell with 7 doesn't.
Which counting function to use
| You want to count | Formula | What it counts |
|---|---|---|
| Any text | =COUNTIF(A2:A10,"*") | Cells holding text only |
| One exact word | =COUNTIF(A2:A10,"Shipped") | Cells that say exactly Shipped |
| A word anywhere in the cell | =COUNTIF(A2:A10,"*urgent*") | Cells containing urgent anywhere |
| Anything at all | =COUNTA(A2:A10) | Every non-empty cell |
| Only numbers | =COUNT(A2:A10) | Cells holding numbers |
| Empty cells | =COUNTBLANK(A2:A10) | Blank cells in the range |
Counting a particular word, like Shipped
Replace the asterisk with the word in quote marks: =COUNTIF(C2:C10,"Shipped") counts the orders marked Shipped in column C. Microsoft says COUNTIF isn't case sensitive, so shipped, Shipped and SHIPPED all count.
Even better, put the word in a cell. If E2 says Shipped, =COUNTIF(C2:C10,E2) counts that status, and you can change E2 to Pending without touching the formula. A small summary table of statuses down column E, each with its own COUNTIF beside it, gives you an instant overview of your order book.
To count cells that contain a word somewhere inside longer text, wrap it in asterisks. =COUNTIF(D2:D10,"*gift*") counts every order note that mentions gift, whatever else it says.
Why your text count comes out wrong
The catch is hidden characters. Microsoft warns that leading or trailing spaces, a mix of straight and curly quote marks, or non-printing characters can make COUNTIF return unexpected results. Shipped with a space after it isn't the same as Shipped to an exact-match COUNTIF, so data pasted from a website or an email often undercounts.
The fix Microsoft suggests is TRIM or CLEAN. Add a helper column with =TRIM(C2), copy it down, and count that column instead. Or count with wildcards on both sides, which tolerates stray spaces at either end.
Watch COUNTA too. Microsoft says it counts any cell with content, including error values and formulas that return empty text. A column of formulas that look blank can still give a full count with COUNTA, which is one reason to use COUNTIF with the asterisk for text.
Counting with more than one condition
COUNTIF handles one condition. If you want orders that are Shipped and going to Leeds, use COUNTIFS, which takes pairs of ranges and criteria: =COUNTIFS(C2:C10,"Shipped",D2:D10,"Leeds"). Microsoft points to COUNTIFS whenever you need multiple criteria.
What COUNTIF can't count
- Colours. Microsoft says COUNTIF can't count cells by background or font colour. Use a status column instead of colour-coding.
- Very long text. Matching strings longer than 255 characters gives wrong results.
- Literal asterisks or question marks. Put a tilde (~) in front, as in "~*", to find the actual symbol.
If you're turning those counts into averages, such as items per order, my guide to dividing in Excel covers the next formula. If a count looks too high, my guide to highlighting duplicates in Excel helps you spot entries that have been typed in twice.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
A worked example: an order status summary
Here's how I'd set it up on a simple orders sheet. Column A holds the client, column B the order date and column C the status. In E2 to E4 type the three statuses you use, say Pending, Packed and Shipped. In F2 type =COUNTIF(C2:C10,E2), then do the same in F3 and F4, pointing at E3 and E4. If you'd rather copy it down, press F4 while the cursor sits on the range so Excel locks it and it doesn't slide as you copy.
Now you've got a live count of each status that updates as you change column C. Add =COUNTIF(C2:C10,"*") underneath to check the three counts add up to the total number of orders with a status. If they don't, there's a typo or a stray space somewhere, and that's your cue to tidy the list.
It's a tiny dashboard, but it's the one I'd want on any sheet that tracks work in progress. You don't need a pivot table or any add-ins, and anyone who opens the file can see at a glance what's waiting to go out. If the list grows past row 10, just widen the ranges, or turn the list into a table so the ranges grow on their own.
My verdict
Use =COUNTIF(range,"*") for any text, the word itself for an exact label, and asterisks around a word to find it inside longer text. If the count looks low, suspect stray spaces before you suspect the formula. I'd swap colour-coding for a dedicated status column too, because Excel can count words but not colours.
Frequently asked questions
How do I count cells that contain any text in Excel?
Use =COUNTIF(range,"*"). The asterisk matches any text, so numbers and blanks are left out.
Is COUNTIF case sensitive?
No. Microsoft says COUNTIF treats upper and lower case as the same.
What is the difference between COUNTA and COUNTIF with an asterisk?
COUNTA counts every non-empty cell, including numbers and errors. COUNTIF with an asterisk counts text only.
Can Excel count cells by colour?
Not with COUNTIF. Microsoft says it can't count by background or font colour.
How do I count cells that contain a word anywhere?
Wrap the word in asterisks, for example =COUNTIF(A2:A10,"*gift*").
