How to Count Cells With Text in Excel (Any or Specific Text)

To count cells with text in Excel, use the COUNTIF function. The criteria you give it decides what gets counted:

• Any text: =COUNTIF(A2:A10,"*")
• Specific text (the whole cell): =COUNTIF(A2:A10,"Apple")
• Cells that contain the text anywhere: =COUNTIF(A2:A10,"*Apple*")
• Two or more conditions: =COUNTIFS(A2:A10,"*Apple*",B2:B10,"North")

COUNTIF is not case-sensitive, so “Apple” also counts “apple” and “APPLE”. When upper and lower case matter, use SUMPRODUCT with EXACT or FIND instead. I’ll show you every one of these below 👇

Download our free sample workbook here to practice the topic firsthand. Let’s get started 🚀

Table of Contents

How to Count Cells With Any Text

Want to know how many cells in a range contain text, no matter what the text says? Use COUNTIF with an asterisk (*) as the criteria:

The asterisk is a wildcard that matches any sequence of characters, so every cell with text in it is counted.

On the COUNTIF sheet of the sample workbook (shown in the next section), all nine cells in A2:A10 contain a fruit name, so this formula returns 9:

COUNTIF with "*" only counts text. Cells with numbers or dates are skipped. This formula gives the same result:

ISTEXT returns TRUE for every cell that contains text. The double minus (--) turns TRUE and FALSE into 1 and 0, and SUMPRODUCT adds them up.

Want to count every cell that isn’t empty, numbers included? Use COUNTA instead. Here’s how to count cells that are not blank.

How to Count Cells With Specific Text Using COUNTIF

The most popular way to count cells with specific text is the COUNTIF function. You give it a range and the text to look for, and it counts the cells that match.

Here’s the syntax:

where

range is the range of cells you want to count in

text is the text you want to count. Type it in double quotation marks, or refer to a cell that contains it.

Let’s see an example.

Say, we have the following sample data set.

In cell B2, let’s count how many times the word Apple appears in column A.

To do that,

Step 1) Select cell C2.

Step 2) Type in the word Apple 🍏

Step 3) Enter the following formula in cell B2:

Step 4) Press Enter.

Step 5) The formula returns the number of cells in A2:A10 that contain the word Apple.

The formula returns 5. “Apple” appears three times and “apple” twice, and COUNTIF counts all five cells, no matter if the word starts with a capital letter or not.

You can also type the text straight into the formula: =COUNTIF(A2:A10,"Apple") gives the same result. The cell reference just makes it easy to change what you count.

We will later see how you can count cells with case-sensitive text too.

How to Count Cells That Contain Specific Text (Partial Match)

The formula above only counts cells where the whole cell is “Apple”. But what if the text is part of a longer string, like “Apple&” or “Green apple”? 🤔

To count cells that contain the text anywhere, we use wildcard characters:

• An asterisk (*) matches any number of characters, including none.
• A question mark (?) matches exactly one character.

If the text comes first and is followed by unknown characters, write it as:

If the text can be anywhere in the cell, put an asterisk on both sides:

Let’s see how to do this below with an example.

I want to find the word Apple in my data set but this time, my data has the word apple written in different ways.

To count the cells that start with Apple,

Step 1) Type in the following COUNTIF formula in cell C2:

Step 2) Press Enter.

The result is 3: the three cells that say “Apple&”.

Now count the cells that contain Apple anywhere in the text.

Step 3) Type in the following COUNTIF formula in cell C3:

Step 4) Press Enter.

This time the result is 5, because the two “&apple” cells contain apple too (and COUNTIF ignores case).

To count cells that contain an actual asterisk or question mark, type a tilde (~) in front of it. For example, =COUNTIF(A2:A10,"*~?*") counts cells that contain a question mark. There’s more in my guide to wildcards in Excel.

It’s that simple. Try it yourself now! 😃

How to Count Cells With Text Using Multiple Criteria (COUNTIFS)

COUNTIF takes one condition. To count cells that meet two or more conditions at the same time, use COUNTIFS:

COUNTIFS only counts a row when all the conditions are true. Every extra range must have the same number of rows and columns as the first one.

For example, say column A holds fruit names and column B holds the store that sold them. This formula counts the rows where the fruit contains “apple” and the store is North:

Wildcards work in COUNTIFS just like in COUNTIF, and you can add up to 127 range and criteria pairs.

Count cells with one text OR another

COUNTIFS uses AND logic: every condition must be true. To count cells that contain “Apple” or “Banana”, add two COUNTIF formulas together:

Just make sure the two conditions can’t match the same cell. With "*apple*" and "*app*", for example, a cell with “apple” would be counted twice.

Learn more about both functions in my COUNTIF and COUNTIFS tutorial.

How to Count Cells With Case-Sensitive Specific Text

Depending on the situation, you might need to count cells with case-sensitive values. COUNTIF can’t do that, because it ignores upper and lower case.

So we build our own formula to count lowercase and uppercase text values, depending on whether you want an exact or a partial match.

Let’s see how to do both.

How to Count Case Sensitive Cells with Exact Match

As the name suggests, exact match strictly compares the text with every cell in the range 📑

This means if your text is KL-34, it counts KL-34 but not kL-34 or KL,34. To count cells with specific text and make the count case-sensitive, we use this combination of Excel functions:

where,

EXACT function compares the text with each cell in the range. It returns TRUE for cells that match exactly (upper and lower case included) and FALSE for the rest.

The double minus or double unary (--) converts the TRUE and FALSE values to 1’s and 0’s, so we can add them up.

SUMPRODUCT function adds up all the 1’s and 0’s and returns the count.

Let’s now see its usage step-by-step through an example.

Say, we have the following sample data.

We want to count the cells that say Apple, with a capital A.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula returns 1. Only one cell (A3) is exactly “Apple”. “Apples” has an extra letter, and “apple” with a lowercase a doesn’t match the case.

Easy, right?

How to Count Case Sensitive Cells with Partial Match

Unlike exact match, partial match looks for the text anywhere in a cell. The formula is a little trickier, but don’t worry, I’ve got you.

We combine three Excel functions to count a case-sensitive partial match in a range:

where,

FIND looks for the text in each cell of the range. If it finds it, it returns the position of the first character. If not, it returns a #VALUE! error. FIND is case-sensitive and doesn’t support wildcards. We don’t care about the position itself, only whether we get a number or an error.

ISNUMBER turns every number returned by FIND into TRUE and every error into FALSE. The double unary (--) then converts the TRUE and FALSE values to 1’s and 0’s.

SUMPRODUCT adds up the 1’s and 0’s, which gives us the number of cells where the text is found.

Let’s now see an example to understand how this formula works better.

We will use the same word as before (Apple) to search for in our data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter

The formula returns 3: “Apple” and the two “Apples” cells. “appleh” is not counted, because its a is lowercase.

Isn’t that cool? 🧐

Conclusion

To sum up, COUNTIF handles most text counting in Excel: "*" counts any text, "text" counts an exact match and "*text*" counts cells that contain the text. Use COUNTIFS when you have more than one condition, and SUMPRODUCT with EXACT or FIND when upper and lower case matter. You can also combine SUMPRODUCT with ISNUMBER or ISTEXT to count exactly what you need.

You can use the COUNTIF function for other purposes as:

How to Count Not Blank Cells

How to Use Wildcards in Excel

How to Count the Number of Cells in a Range

How to Count Words in Excel

Frequently Asked Questions

How do I count cells with any text in Excel?

Use =COUNTIF(A2:A10,"*"). The asterisk matches any text, so the formula counts every cell in A2:A10 that contains text. Cells with numbers are not counted.

How do I count cells that contain specific text?

For cells that contain only that text, use =COUNTIF(A2:A10,"Apple"). For cells that contain the text somewhere (like “Green apple”), add asterisks: =COUNTIF(A2:A10,"*Apple*"). If the text is in a cell, say D2, use =COUNTIF(A2:A10,"*"&D2&"*").

Is COUNTIF case-sensitive?

No. COUNTIF counts “apple”, “Apple” and “APPLE” as the same text. For a case-sensitive count, use =SUMPRODUCT(--EXACT("Apple",A2:A10)) for an exact match or =SUMPRODUCT(--ISNUMBER(FIND("Apple",A2:A10))) for a partial match.

How do I count cells that do not contain specific text?

Put "<>" (not equal to) in front of the text: =COUNTIF(A2:A10,"<>Apple") counts the cells that are not exactly “Apple”, and =COUNTIF(A2:A10,"<>*Apple*") counts the cells that don’t contain “Apple” anywhere. Both count empty cells too, so make the range exactly as big as your data.

How do I count how many times a word appears in a column?

COUNTIF counts cells, not words. A cell that says “apple, apple” counts as one. To count every occurrence of a word, also when it appears several times in one cell, see my tutorial on how to count words in Excel.

What is the difference between COUNT, COUNTA and COUNTIF?

COUNT only counts cells with numbers. COUNTA counts every cell that isn’t empty (text, numbers, errors and empty text from formulas). COUNTIF counts the cells that meet a condition you set, like "*" for any text. Here’s more on the COUNT and COUNTA functions.

Why does my COUNTIF formula return 0 or the wrong number?

Check the data for leading or trailing spaces, nonprinting characters and a mix of straight and curly quotation marks. Any of them can make COUNTIF miss a match, and the TRIM and CLEAN functions can clean them up (here’s how to remove extra spaces in Excel). Also note that COUNTIF returns wrong results for text longer than 255 characters.