You can carry out simple arithmetic operations on excel, and it mainly involves numbers. However, you can also manipulate texts and other special characters in your cells. When dealing with texts and numbers, you may wish to know the number of cells containing texts. There are several formulas in excel that you can use for this, and your choice depends on the situation. Now, let's take a look at some of the formulas for counting the number of cells that contain texts in an excel spreadsheet.
Firstly, you should choose an empty cell where you want your counting result to display.
To count the number of cells that contains text only, you can paste the formula below into the formula bar:
Formula 1
=COUNTIF(range,"*")
With this formula, you can ascertain the number of cells that do not contain numbers, errors, or blank.
For instance;
=COUNTIF(A1: B4,"*") will only count the number of cells that contain texts in row A1, column B4.
Sum product functions
You can also use the SUMPRODUCT functions as well as ISTEXT functions in counting the number of cells that contain text.
SUMPRODUCT(–ISTEXT(range)) or SUMPRODUCT(ISTEXT(range)*1)
When considering the range, A2: B4, you can count the number of cells containing texts using the formulas below:
=SUMPRODUCT(–ISTEXT(A1:F11))
=SUMPRODUCT(ISTEXT(A1:F11)*1)
You can change the range of the cells whenever you want. More so, you can tweak the formula to count the number of cells containing a specific text.
How to exclude spaces, special characters, and empty strings when counting texts in a cell:
The formulas above count texts only, but it also counts some cells that have the traits of texts in them. Such cells do not really contain texts. They seem to be empty, but your countif functions will count them, and the result will be confusing. For instance, cells with apostrophes in front are always counted as texts. To exclude spaces and special characters when counting the number of cells that contain text, use the formula below:
=COUNTIFS(A2:B4,"*", A2:B4, "<> ")
Excel formula to count the number of cells with specific text
Now, you may desire to know the number of cells that contain a particular text. Use this:
=COUNTIF(range, "text")
If you want to count the number of cells in the range A1: C5 that contain the exact word "Gold," you can enter the formula as follows:
=COUNTIF(A1: C5, "Gold")
In this case, it will only count cells that contain the word "Gold" only. Cells that contain additional words before or after "Gold" will not be counted.
However, we can tweak the formula so that we can count cells that contain "Gold" as part of the contents.
=COUNTIF(range, "*text*")
To count the number of cells within the range A2: B4 containing "Gold" anywhere, use the formula:
=COUNTIF(A2:B4, "*Gold*")
Please note that these formulas are not case-sensitive.