COUNTIF examples: how to count text, numbers, blanks and duplicates in Excel

What does COUNTIF do in Excel?

COUNTIF counts how many cells in a range meet one condition. =COUNTIF(B2:B16,"Clothing") runs through cells B2 to B16 and finds six that say Clothing. It counts cells, it doesn't add up amounts. If you want to know what that clothing brought in, you need SUMIF. In a Dutch version of Excel, by the way, COUNTIF is called AANTAL.ALS.

The basics, explained on a sheet that lights up as you go, are in our lesson COUNTIF: counting what matches. This article is the reference to go with it: counting with operators such as > and <>, with a limit in a cell, with wildcards, blank cells and duplicates. And you'll see why COUNTIF sometimes returns 0 when you're sure there's something to count.

How do you write a COUNTIF formula?

COUNTIF takes two arguments: the range Excel counts in, and the criterion a cell has to meet (Microsoft). There's room for exactly one criterion. If you need to check two things at once, such as the category and the price, you'll want COUNTIFS, which comes up later in this article.

=COUNTIF(range,criteria)

The comma between the arguments isn't fixed. According to Microsoft, the most common list separators are the comma and the semicolon, and which one you get depends on your operating system's locale and your Excel settings, not on Windows or Mac (Microsoft). In an English Excel set up for a country where the comma is the decimal mark, as in much of mainland Europe, the same formula reads =COUNTIF(B2:B16;"Clothing").

Example: how many items of clothing did the shop sell?

The Second Chance charity shop in Gloucester is the fourth client in our Excel course. Its coordinator, Yasmina, puts every sale of the week on its own row: the item, the category, the price, the day and the volunteer at the till. Every example in this article works on this sheet, with all fifteen sales, so you can check each result yourself.

=COUNTIF(B2:B16,"Clothing")

The formula returns 6: Dress green, T-shirt S, Dressing gown, T-shirt XL, Dress black and T-shirt M. Excel runs down column B and adds one for every cell that equals the criterion. Upper and lower case play no part (Microsoft), so "clothing" gets you 6 as well. The quotation marks do matter. Without them, Excel reads the word as the name of something else, and that doesn't exist.

Now try it yourself, on another column. The exercise runs on the real Second Chance sheet, and you type the formula the way your own Excel does: with commas and English names, or with semicolons and the names of your language. First find the column that holds the days.

How do you count numbers greater than or less than a value?

Put a comparison operator in front of the number, and put the operator and the number together in quotation marks: ">10", not >10. That way you count everything above, below or apart from a limit. Here are the six operators, each used on the prices in column C or the categories in column B of the sheet above:

Watch the difference between < and <=. £5 or less gives 7, less than £5 gives 6: the game at exactly £5 drops out with "<5". Limits like these are the classic trap. When a question says "up to", "from" or "more than", read it twice before you pick the operator. You'll practise them in the lesson criteria with operators.

Dates work the same way, because Excel stores dates as sequential serial numbers (Microsoft). If A2:A20 holds dates, =COUNTIF(A2:A20,">="&DATE(2026,1,1)) counts every date from 1 January 2026 on. With DATE there's no doubt about which number is the day and which is the month.

How do you use a cell as the criterion?

Join the operator and the cell with &. Yasmina keeps her price limit of £10 in G2, and =COUNTIF(C2:C16,">"&G2) turns that into the criterion ">10": 4 items. When she changes the limit, the formula recounts straight away. Microsoft builds its own example the same way, with "<>"&B4 (Microsoft).

">G2" gives you 0. Inside the quotation marks, G2 is just text, so Excel looks for prices greater than the word G2. The operator goes inside the quotation marks, the reference outside.

Try it yourself, with the limit in G2. This time we also check your formula on a second, hidden version of the sheet, with other prices and another limit. That way you'll know at once whether your count follows the limit when it changes.

Your formula ran in your own browser, on a real calculation engine. We check the result, not the spelling, so a different formula that finds the same count is fine too. Anyone who typed ">10" got the right number here and the wrong one on the hidden sheet, with its other limit. That's exactly what happens in real life when someone changes the limit.

How do you count with wildcards?

A wildcard lets you count text you don't know exactly. The asterisk * stands for any number of characters, including none. The question mark ? stands for exactly one character (Microsoft). No item is called just Dress, but a wildcard finds them anyway:

To find an actual asterisk or question mark, put a tilde (~) in front of it. And "*" on its own counts every cell with text, Microsoft says: in column A, =COUNTIF(A2:A16,"*") returns 15, in column C it returns 0, because that's all numbers. You'll find more examples on the same items in the lesson wildcards.

How do you count blank and non-blank cells?

Use "" as the criterion to count blank cells and "<>" to count everything that isn't blank. The Green Fingers allotments in Harrogate, another client in the course, track each plot's tenant and harvest. Plot P04 is vacant, and P09 grew no tomatoes this year:

=COUNTIF(B2:B11,"") returns 1, the vacant plot, and =COUNTIF(B2:B11,"<>") returns 9 rented plots. Nothing follows the "not equal to" operator, so every cell that isn't empty counts. Excel also has functions of its own for counting. Here's how they differ on the names in column B and the kilos in column D:

COUNT doesn't see the names, because they're text, and "*" doesn't see the kilos, because they're numbers. So decide what you want to count first, and pick the function after. One detail: COUNTBLANK also counts a cell with a formula that returns "", but not a cell holding 0 (Microsoft). You'll practise COUNT and COUNTA in the lesson counting what's there.

How do you find duplicates with COUNTIF?

Let COUNTIF count, for every row, how often its value appears in the whole column. If that's more than once, the value is a duplicate. On the charity shop's sheet, put this formula in F2 and fill it down to F16:

=COUNTIF($B$2:$B$16,B2)>1

Every row returns TRUE except row 9: the radio is the only item in the Electrical category. The dollar signs lock the range while B2 moves on to B3, B4 and so on. Leave them out and the range slides down with the formula, until the one in F16 only looks at B16 and the empty cells below it. On the items in column A, the same formula returns FALSE fifteen times: no item appears twice.

To flag only the second and later appearances, let the range grow. With =COUNTIF($B$2:B2,B2)>1, each row looks only at itself and the rows above it. Rows 2, 3, 4, 7 and 9 now return FALSE, because that's where a category appears for the first time. So there are five categories, and every TRUE is a repeat. Rather see a word than TRUE? Wrap the test in the IF function.

Why is COUNTIF counting wrong?

Usually because a cell looks different from what it is: a number that's really text, a space you can't see. Or because the formula asks something other than what you meant, such as AND where you wanted OR. These are the causes we come across most, each with its fix.

A number is stored as text

If someone enters the radio's £15 as text in C9, =COUNTIF(C2:C16,">10") returns 3 instead of 4. With an operator, COUNTIF only counts real numbers. Excel usually flags such a cell with a small green triangle. Select the cells, click the warning and choose Convert to Number (Microsoft).

A space or an invisible character

"Clothing " with a trailing space is a different value to Excel than "Clothing". If that space sits in B2, "Clothing" only counts 5. Microsoft recommends clearing out leading and trailing spaces, nonprinting characters and a mix of straight and curly quotation marks, with TRIM or CLEAN (Microsoft).

Text longer than 255 characters

When COUNTIF has to match text longer than 255 characters, it returns a wrong result, Microsoft warns on the same page. The fix: cut the criterion into pieces and join them with &, as in =COUNTIF(A2:A5,"long string"&"another long string"). With short values, such as categories or names, you'll never run into this.

A formula from a European colleague

A formula such as =COUNTIF(B2:B16;"Clothing") won't work in an Excel with UK or US settings. Swap the semicolons for commas and it will. In a Dutch version you'd also see a different name, =AANTAL.ALS(B2:B16;"Clothing"). And watch decimals in a criterion, because the decimal mark changes too: ">2,5" becomes ">2.5".

Mixing up OR and AND

If you want clothing or books, add two COUNTIFs together: =COUNTIF(B2:B16,"Clothing")+COUNTIF(B2:B16,"Books") returns 8. Microsoft does the same in its example with apples and oranges. If two conditions must both be true, use COUNTIFS. A row only counts when all its criteria are met (Microsoft).

=COUNTIFS(B2:B16,"Clothing",C2:C16,">5")
=COUNTIFS(B2:B16,"Clothing",B2:B16,"Books")

The first formula finds three items of clothing over £5. The second returns 0, because one cell can't be Clothing and Books at the same time: for "clothing or books" you need the sum of two COUNTIFs. You'll practise COUNTIFS in the lesson several conditions.

Which functions are like COUNTIF?

COUNTIF has a whole family. SUMIF (Microsoft) and AVERAGEIF (Microsoft) take their criterion in the same form, with the same operators and wildcards. Know one and you're halfway to the other. The difference is what they do with the rows that match: count them, add them up or average them. On the charity shop's sheet:

You'll find more examples, with the same kind of criteria and the same shop, in SUMIF with text, dates and wildcards and how to calculate an average in Excel. And to see the twelve formulas you'll need most, COUNTIF among them, read 12 basic Excel formulas everyone should know.

Counting is easiest to learn on a sheet you can see. In module 4 of our Excel course you do just that at Second Chance, from your first COUNTIF to wildcards and several conditions. Excel practice exercises with explanations shows how those exercises work.