Count Cells with Text in Excel (with the COUNTIF Formula)Learn how to count cells with text in Excel

Written By:
Patrick Louie

Excel is a versatile app that can store a variety of data types.

You may have already worked with Excel spreadsheets that contain a combination of numbers, text, dates, special characters, etc.

This is why experts in highly technical fields still use Excel.

Now when you work with a large dataset, you may come across the difficulty of having to sort and retrieve a particular type of data.

For example, in a dataset that’s composed of tons of numerical and text values, it can be hard to retrieve only the ones that contain text.

To lessen a bit of the difficulty, you can count the number of cells that contain a particular data type.

The COUNTIF is one such function that can help you in that regard.

In this article, I’ll be showing you how to use the COUNTIF (and as an extension, the COUNTIFS function) to count cells that contain text in Excel.

Be it counting cells with any text value or those that with a specific text value, this article has got you covered.

We’ll also be using a variation of the COUNTIF formula to exclude empty strings and spaces in the count.

By the end of the article, you should be able to quickly count the number of cells that contains text in Excel.

That should help in sorting and retrieving a particular data type, especially from a large dataset.

Let’s get started.

Count the Number of Cells that Contain Any Text in Excel

Let’s start first with counting cells that contain any text values.

To do so, we’ll be using the * (asterisk) wildcard as the criterion for the COUNTIF function.

What the * does is it acts as a substitute for any number of characters.

So whether a cell contains a text that’s one character long or a text that has 15 characters in it, so long as the COUNTIF criterion is an * (asterisk), that cell will be counted.

The formula for using COUNTIF to count cells that contain any text is as follows:

=COUNTIF(range, “*”)

Where

range – refers to the range of cells from which the COUNTIF function will count cells that meet the criterion/criteria

To illustrate how the formula works, let’s say we have the following dataset:

This dataset has 10 cells that contain various data types (located in cells A1:A10). We’ll be using the COUNTIF function to count only the cells that contain text:

And as per the illustration above, there are 6 cells that contain text.

What the formula counts

The formula counts the cells that contain any of the following:

  • Any text (the number of characters doesn’t matter)
  • Special characters
  • Numbers or dates that are formatted as text
  • Empty strings (“”), apostrophe (‘), space(s), or invisible non-printing characters; these characters will result in a cell that is visually blank but isn’t technically empty

What the formula doesn’t count

The formula excludes the cells that contain any of the following from the count:

  • Number (i.e. numerical values)
  • Dates (e.g. 2/1/2023)
  • Error values (e.g. #DIV/0!)
  • Blank cells
  • Logical values (such as TRUE or FALSE)

Other formulas you can use

Aside from the COUNITIF formula above, you can also the following formulas:

=SUMPRODUCT(–ISTEXT(range))

-or-
=SUMPRODUCT(ISTEXT(range)*1)

Both formulas will yield the same result as the COUNTIF formula.

Count the Number of Cells that Contain Any Text in Excel (Excluding Empty Strings and Spaces)

Our COUNTIF formula above includes all cells that contain text in the count.

It also includes seemingly blank cells that contain space(s) or particular special characters (“”, ‘). To illustrate my point, here’s an example:

In the above illustration, the COUNTIF formula counts 10 cells with text within the range A1:14.

However, if you manually count the cells with text, the count should only 6. What gives?

This is because cells A11:A14 contain characters that make the cells visually blank (even though they’re not actually empty).

Cell A11 has a space, cell A12 has an apostrophe, cell A13 has an empty string, and cell A14 has another space.

These cells were included in the count, hence why the COUNTIF function counts 10 cells with text.

To exclude these cells from the count, we’ll be using the COUNTIFS function, which is a variation of the COUNTIF function. It will allow us to set multiple criteria. The formula should look like this:

=COUNTIFS(range,”*?*”, range,”<> “)

Where

range – refers to the range of cells from which the COUNTIF function will count cells that meet the criterion/criteria

In the above formula, we used two criteria: “*?*” and “<> “. Both these criteria exclude cells that don’t have at least one text character. To illustrate, let’s use the formula on the same dataset:

There you have it. The COUNTIFS formula is now showing a count of 6 rather than 10.

Count Cells that Contain a Specific Text in Excel

In the above formulas, we counted the cells that contain any text value. We did it by using the wildcard character * (asterisk) as the criterion/criteria.

Now, if we want to count only the cells that contain a specific text, we only need to set the criterion/criteria to the text that we want to count. Depending on the formula, we can make an exact or partial.

If we want to count the number of cells that exactly contain the specific text, we’ll be using this formula:

=COUNTIF(range, “text“)

Where

range – refers to the range of cells from which the COUNTIF function will count cells that meet the criterion/criteria

text – refers to the specific text from which the COUNTIF function will base its count

If we want to count the number of cells that may contain (partial match) the specific text, we’ll be using this formula:

=COUNTIF(range, “*text*“)

Where

range – refers to the range of cells from which the COUNTIF function will count cells that meet the criterion/criteria

text – refers to the specific text from which the COUNTIF function will base its count

Here’s an example of how the two formulas work:

The first formula =COUNTIF(A1:A12,”Cat”) counts only the cells that exactly contain the text “Cat”.

The formula returns a count of 1 since there’s only one cell that exactly contains the text “Cat”, which is cell A1.

On the other hand, the second formula =COUNTIF(A1:A12,”*Cat*”) counts the cells that can include the text “Cat” anywhere in them.

It counts 4 cells that contain the text “Cat”.

Conclusion

And those are the ways you can count cells with Text in Excel. We mainly used the COUNTIF function to do the job.

However, if you want to set multiple criteria, you can use the COUNTIFS function. You should now be able to quickly count the cells that contain in Excel.