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.
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,”*”)
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,”*”)
Our final spreadsheet now looks like this:
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”.
Comments are closed.