- How the COUNTIF formula in Excel is built
- Writing your first COUNTIF in Excel
- Microsoft's COUNTIF examples worked through
- Combining two COUNTIF formulas in Excel
- Using wildcards in COUNTIF for partial matches
- Why COUNTIF in Excel returns the wrong number
- What COUNTIF in Excel can't do
- COUNTIF ideas for small businesses and creators
- Frequently asked questions
To use COUNTIF in Excel, type =COUNTIF(range, criteria) in an empty cell, for example =COUNTIF(A2:A10,"Instagram"), and press Enter. Text and comparisons such as ">55" go in quotation marks. Wrong counts usually come from stray spaces, which Microsoft says TRIM or CLEAN can fix.
How many enquiries came from Instagram? How many orders are still marked Pending? How many workshop places have been booked? If the answer means counting cells that match something, COUNTIF does it in one line, and it updates by itself as your list changes.
It's one of the first formulas I'd teach any small business owner, because it turns a long list into a quick answer. Here's how it works, the examples Microsoft uses, and the small things that make it return the wrong number. You don't need any maths, just a tidy column of words or numbers to count.
How the COUNTIF formula in Excel is built
COUNTIF has just two parts, according to Microsoft:
=COUNTIF(range, criteria)
- range is the group of cells you want to count, such as a Source column.
- criteria is what a cell must match to be counted. Microsoft says it can be a number, an expression, a cell reference or a text string.
Put in plain English: count the cells in this range that match this. Text and expressions go in quotation marks; a cell reference doesn't.
Writing your first COUNTIF in Excel
Say your enquiry list has the source of each lead in column A, from A2 down to A10.
- Click an empty cell where you want the answer.
- Type =COUNTIF(
- Select the cells A2 to A10, then type a comma.
- Type "Instagram" with the quotation marks, then close the bracket.
- Press Enter.
Your formula reads =COUNTIF(A2:A10,"Instagram") and returns how many enquiries came from Instagram. Change the word to Email or Referral to count the others.
Microsoft's COUNTIF examples worked through
Microsoft's help page uses a tiny sample list: apples, oranges, peaches and apples in A2 to A5, with the numbers 32, 54, 75 and 86 beside them in B2 to B5. Here's what each example formula returns:
| Formula | What it counts | Result |
|---|---|---|
| =COUNTIF(A2:A5,"apples") | Cells that say apples | 2 |
| =COUNTIF(A2:A5,A4) | Cells matching whatever is in A4 (peaches) | 1 |
| =COUNTIF(B2:B5,">55") | Numbers greater than 55 | 2 |
| =COUNTIF(B2:B5,"<>"&B4) | Numbers not equal to the one in B4 | 3 |
| =COUNTIF(A2:A5,"*") | Any cell containing text | 4 |
Notice the pattern for comparisons. The operator and the number go inside quotation marks together, as in ">55". If you want to compare against a cell instead, join the operator to the cell with an ampersand, as in "<>"&B4. That's the version I use most, because you can change the number in one cell and the count updates.
Combining two COUNTIF formulas in Excel
Because COUNTIF only takes one condition, Microsoft shows a neat trick for either-or questions: add two COUNTIFs together. In its sample, =COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3) counts apples plus oranges and returns 3.
You can subtract them too. Microsoft's example =COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,"<=85") returns 1, the one number above 85. For a small business, the adding version is the useful one: enquiries from Instagram plus enquiries from Facebook gives you a single social media total without building a new column. If the two-part questions start to pile up, though, that's your cue to switch to COUNTIFS.
Using wildcards in COUNTIF for partial matches
Sometimes the text varies a little. Wildcards help:
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.
- An asterisk matches any run of characters. "*course*" counts every cell with course anywhere in it.
- A question mark matches exactly one character. Microsoft's example "?????es" counts words with five characters followed by es, which returns 2 in its sample.
- To count a real question mark or asterisk, put a tilde in front of it, such as ~?
Wildcards are great for messy data, like a list where some people typed Instagram and others typed Instagram DM.
Why COUNTIF in Excel returns the wrong number
Microsoft lists several common causes. These are the ones I see most:
- Stray spaces. A cell that says Instagram with a space on the end won't match "Instagram". Microsoft says leading spaces, trailing spaces and nonprinting characters cause unexpected results, and suggests the CLEAN or TRIM functions to tidy them.
- Very long text. Microsoft says COUNTIF gives incorrect results when matching text longer than 255 characters.
- A #VALUE! error. That happens when the formula points to another workbook that's closed. Microsoft says the other workbook must be open.
- Quotation marks missing around text or comparisons, so Excel doesn't know what you mean.
Good news on capital letters: Microsoft says criteria aren't case sensitive, so apples and APPLES count the same.
What COUNTIF in Excel can't do
Two limits to know. First, Microsoft says COUNTIF uses only a single criterion. If you need to count rows that match two things, such as Instagram enquiries that are also marked Booked, use COUNTIFS, which Microsoft points to for multiple criteria.
Second, Microsoft says COUNTIF can't count cells by their background colour or font colour. If you've been colour-coding a list, add a Status column with words instead, and count those.
COUNTIF ideas for small businesses and creators
| Question | Example formula |
|---|---|
| How many leads came from LinkedIn? | =COUNTIF(A2:A10,"LinkedIn") |
| How many tasks are ticked? | =COUNTIF(C2:C10,TRUE) |
| How many orders are not yet shipped? | =COUNTIF(D2:D10,"<>Shipped") |
| How many posts mention a course? | =COUNTIF(B2:B10,"*course*") |
Counting leads by source is the quickest way to see which channel is pulling its weight. If Reddit is on your list, my guide to finding leads on Reddit pairs nicely with a COUNTIF tally. And if your tracker lives in the browser, the Google Sheets SUM walkthrough covers the next formula you'll want.
Frequently asked questions
How do I use COUNTIF in Excel?
Type =COUNTIF(range, criteria) in an empty cell, for example =COUNTIF(A2:A10,"Instagram"), and press Enter. It counts the cells in the range that match.
Is COUNTIF case sensitive?
No. Microsoft says criteria aren't case sensitive, so apples and APPLES match the same cells.
How do I use COUNTIF with more than one condition?
Microsoft says COUNTIF uses only a single criterion. Use COUNTIFS when you need to match several conditions.
Why is my COUNTIF returning zero?
Usually stray spaces or slightly different text. Microsoft suggests TRIM or CLEAN to remove hidden spaces and characters, or use wildcards like an asterisk.
Can COUNTIF count coloured cells?
No. Microsoft says COUNTIF can't count cells based on background or font colour. Add a text Status column and count that instead.
