Excel has a number of functions to count occurrences. There is one to count numbers, cells that meet criteria, and one to even count blanks! However, there isn’t exactly a function to count text values in Excel. In order to count cells with text, you need to use either the COUNTIF function with the asterisk wildcard or nest the SUMPRODUCT function with the ISTEXT function.
You can use one of these methods to the best of your convenience. Remember that both of these methods will not count cells that have numbers or symbols. However, these methods also count string values, which are values consisting of letters and numbers.
Use the COUNTIF Function
One of my go-tos, if I want to count the cells containing text in a certain range in Excel, is to use the COUNTIF function. You can either choose to count cells containing any text or string value or choose to count a specific text. This flexibility is what makes the COUNTIF function so functional to use.
Count All Cells with Text
If you want to count texts that contain any text values, you will need to pass the asterisk (*) wildcard as your criterion in the COUNTIF function.
Asterisk is a wildcard that commands Excel to select all text values. When paired with COUNTIF individually, it will count all text values. Here is how you will have to construct the COUNTIF function using the asterisk wildcard in Excel:
=COUNTIF(range, “*”)Let’s put this into an example for a better understanding.
Take a look at this data table. We will be using the above-mentioned formula to count the number of cells containing text in column C. On an empty cell, we entered the following formula:
=COUNTIF(C2:C11, “*”)
Count Cells That Start/End with a Specific Letter
You can use the asterisk wildcard to count cells that end or begin with a specific letter. All you have to do is enter your text then place your wildcard before or after the letter depending on if you want to count cells with data starting or ending with that data.
Note that COUNTIF is not case-sensitive. This means it will consider both “apple” and “APPLE” if you have set the criteria accordingly.
In this table we have two ranges, first, let’s use the asterisk wildcard to count cells that begin with the letter A in column A. Here’s the formula we will be using:
=COUNTIF(A2:A6, “A*”)
Next, in column B, let’s count all cells that contain a text that ends with the letter “e”. This is the formula we will be using for this purpose:
=COUNTIF(B2:B6, “*e”).
Count Exact Text
If you wish to count the occurrence of an exact text, you will have to supply that value as the criteria in the COUNTIF function. Again, remember that COUNTIF is not case-sensitive and will count data from the range that does not match the case that you entered as the criteria.
In this Excel sheet, we have the roles of each student in a classroom. Let’s use the COUNTIF function to see how many roles were assigned to a student named Jared.
=COUNTIF(A2:A11, “Jared”)
Nest ISTEXT Inside SUMPRODUCT
The next method you can use to count cells is to next the ISNEXT function inside the SUMPRODUCT function in Excel.
The ISTEXT function in Excel is a function that returns a boolean value, TRUE or FALSE, depending on if the cell you’ve referenced is a text or not. These boolean values are also denoted by 1 for TRUE and 0 for FALSE, when nested with the INT function.
SUMPRODUCT is a function that adds up corresponding values in a referenced array. After ISTEXT checks whether a cell has a text value or not, the SUMPRODUCT function will add up the returned value.
Let’s combine both of these functions to count the cells containing text in column C. In an empty cell, we entered the following formula:
=SUMPRODUCT(INT(ISTEXT(C2:C11)))
You will first have to reference your range inside the ISTEXT function. The ISTEXT will return TRUE if your cell contains text and FALSE if a cell does not contain text. Then, the INT function will convert TRUE to 1 and FALSE to 0.
When you nest it inside SUMPRODUCT, the returned values from the array are added up. As each cell with text returned 1, the end result will be the total number of cells that contained text in the array.