Excel has functions and formulas that make it possible for you to count the text cells within your worksheet. You can easily count cells with texts in excel using the COUNTIF function and the asterisk symbol (*). These excel formulas help you search your text cells based on specific criteria and in a defined range. In short, what the COUNTIF function tries to say is; where do you want to look? And what do you want to look for? On the other hand, the asterisks symbol represents any number of characters in your excel worksheet.
Using the COUNTIF function to count cells with text in excel
Here we use the formula =COUNTIF (range, "*") to count all cells that will contain any text. Here is an example of how the COUNTIF function is used;
In the example above, the cell range used is B4:B9. From the above results, we see that only three cells contain texts. These are cells B4, B5, and B6. The cells count for the cells that do not contain any data are two. These are cells B7 and B8
In case you want to count cells that have no text, you will use the formula;
=COUNTIF (range; "<>*")
You may also need to restrict your counts to meet specific criteria. You will use the COUNTIFS function to achieve this. Here is how your formula will be;
= COUNTIFS (range, "*", range, "<>")
Using the SUMPRODUCT formula to count cells with any text
You can also get the total number of cells that contain text by combining the SUMPRODUCT and ISTEXT functions. Here is how the formula will look like;
SUMPRODUT (–ISTEXT (range)) OR
SUMPRODUCT (ISTEXT (range) *1)
Here, the ISTEXT function will check if each cell in the specified range of your worksheet contains any text characters. You will get either TRUE results which re [presents cells with text and FALSE results that represent other cells.
We use the double dash signs (–) and the asterisks symbol to coerce the TRUE and FALSE results into 1 and 0. It enables you to get results with an array of ones and zeros.
On the other hand, the SUMPRODUCT function is used to sum all the elements of the array and return results of 1's which will be the number of cells that contain texts.
An example of Excel
From the above results, we can see cells that have been counted and the uncounted ones. Apart from that, we can see counted text cells when different formulas are used.
Using COUNTIF to count cells that contain specific text in excel
Sometimes you may be looking for specific cells that contain certain text or characters in your excel worksheet. You can do this by using the COUNTIF function arguments.
In case you want to match the sample text exactly, you will enter the full text enclosed in quotation marks. Here is the formula to use;
=COUNTIF (range, "text")
In case you want to count cells that have a partial match, you will place the text between two asterisks. It will symbolize any number of characters before and after the text. Here is the formula;
=COUNTIF (range, "*text*")