How to Count Text in Excel

There may come a time in the creation of your Excel spreadsheet where you want to count cells with text. In other words, you want to determine how many cells, within a given range in your spreadsheet, contain some type of text.

The easiest way to count text in Excel is with the COUNTIF function.

The COUNTIF function has many uses but we are going to concentrate specifically on how to use it to count cells with text.

Take a look at the sample Excel spreadsheet below. It contains a list of names in column A and a list of responses in column B. This might be used to track responses to a survey.

count text in Excel - sample 1

We want to determine how many total names we have in our list and how many people have responded. We will fill in the total names in our list in cell C2 and the total responses in cell D2.

Count cells with text – column A

Let’s start with the total names in our list. We will use the COUNTIF function to accomplish this.

=COUNTIF(A2:A100,”*”)

count text in Excel - sample 2

Notice for the range, we used A2:A100. This allows the function to continue calculating the correct number of names, up to 99 names, when we add new names to the list. The number 100 is arbitrary. You could use 100, 500 or even 1,000. It all depends on how many names you anticipate you will have in your spreadsheet.

The second parameter, “*”, is known as a wildcard character which tells Excel to count cells with text in them. It doesn’t necessarily matter what the text itself is. As long as there is something in the cell, it will be counted.

For a more detailed look at the COUNTIF function, check out our Excel Lesson Videos.

Count cells with text – column B

We will use a similar COUNTIF function to compute the number of responses received.

=COUNTIF(B2:B100,”*”)

count text in Excel - sample 3

Our final spreadsheet now looks like this:

count text in Excel - sample 4

As you can see, the COUNTIF functions correctly indicate that we have 12 names on our list and that we have received responses from 7 of the 12 people.

Remember, the COUNTIF function will count cells with text. It doesn’t matter what the text is. While it looks like we are counting “Yes” and “No” responses, Excel would also count any cell in column B where we entered “ABCDEFG” or “Hello”.

count text in Excel - sample 5

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!