Are you someone struggling with finding the count of cells that contain text value in Google Sheets? Then you have reached the right place!
There are scenarios where you have a mixed set of all types of data, such as text, numbers, dates, special characters, etc., and you only want your formula to count the cells that contain text or string values.
There are various functions in Google Sheets that you can use solo as well as in combination with other functions to get this task done.
Throughout this article, let’s curate a few unique ways to get this task done!!!
The Dataset
The dataset I am using for this article is a simple one but mixed with different value types, such as numbers, dates, special characters, blank spaces, etc., along with the text values. See it yourself:
Using the Simple COUNTIF Function and Wildcards
When you have a dataset with mixed values of different types as the one you will use here for this article, counting how many cells contain text becomes a job of comparison.
You want to check at each cell whether it contains text or not. And then return the total count of all such cells.
The COUNTIF function is the most suitable and straightforward way for this case. It checks for a specific condition you provide and then counts only those cells where it is met.
In cell E3, copy and paste the following COUNTIF formula and hit the Enter button to return the count of cells containing only text values.
=COUNTIF(B3:B13, "*")
The “*” (also known as asterisk) is a wildcard character helping the IF part of the formula here to check whether the value from each cell is a text or not. It then produces an array of boolean values, TRUE, and FALSE per row.
{FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}
The COUNT part of the function then counts how many times the value TRUE has appeared in the cells. If you think of the processing, the number 1 represents TRUE, and 0 represents FALSE. So ideally, the COUNT part just shows how many times 1 appeared in the range.
This then results in counting all occurrences of 1’s and returning the value 7 in cell E3.
Note: If you have noticed, the formula also considers the cell containing special characters such as pound, ampersand, dollar, and exclamation mark. They are also considered as the text values; hence, the function returns the count 7 rather than 6. What were you expecting, though?🤔 I would really love to know! 😉
Using SUMPRODUCT and ISTEXT Function
The SUMPRODUCT is a versatile function in Google Sheets that performs two operations, Addition, and Multiplication, simultaneously on an array (or more arrays) based on the context provided.
If combined with ISTEXT, the function returns the count of all cells containing text values as output.
The beauty of the SUMPRODUCT function is that even though it cannot SUM up the boolean array of TRUEs and FALSEs, it uses some other operators smartly to convert them to equivalent numeric values, i.e., 1 and 0, respectively. Then, it sums the array up to produce the output.
The dataset I will be using for this demo is as shown below:
To count the cells containing text, copy and paste the formula below in cell E3 of your sheet and hit the Enter button to execute it.
=SUMPRODUCT(--ISTEXT(B3:B13))
Let’s break down the working of this formula:
The ISTEXT function firstly checks cell by cell whether the value is a text or not. It generates a TRUE/FALSE array. In this context, the ISTEXT function works like the conditional IF.
The —, also called as double unary, then works as a boolean to numeric values converter and converts the array of TRUE and FALSE to an array of numeric 1’s and 0’s.
This part is crucial one in this formula because the SUMPRODUCT can’t sum up the boolean values TRUE and FALSE. It needs an array of numeric values, and this double unary (—) operator precisely does that.
The SUMPRODUCT function now effectively looks like
=SUMPRODUCT({0; 1; 1; 1; 1; 0; 0; 1; 1; 0; 1})
The function then sums up all the 1’s, which represent the count of cells containing text values. Seems interesting?
Another Version of the formula
Few of the users might be reluctant to use the double unary (–) in any formula. It might confuse them, or they might think it is an additional thing to remember that doesn’t follow the general logic of the numbers and formulas.
Such users can use the following version of the formula without this operator.
=SUMPRODUCT(ISTEXT(B3:B13)*1)
The *1 effectively converts the array of TRUEs and FALSEs to a numeric value, and by doing so, allows you to keep the formula simple to remember.
Using COUNTA, FILTER, and ISTEXT
When you are working with finding out the count of cells containing only text values, the combination of the trio COUNTA, FILTER, and ISTEXT provides you with one of the most straightforward and easy-to-understand ways of getting things done.
I will use the mix of data for this demo with all values of different types spread between range B3:B13.
The recipe for this demo is simple: copy or type in the following formula in cell E3 of your sheet and hit the Enter button to see the magic yourself!
=COUNTA(FILTER(B3:B13, ISTEXT(B3:B13)))
Let’s break the logic behind the working of this formula piece-by-piece.
The ISTEXT function works here as a conditional IF and checks whether each cell from the range B3:B13 contains a text value or not. It potentially creates an array of 10 TRUE/FALSE values.
Then, you use this array of the TRUE/FALSE values per cell as a condition inside the FILTER function.
The function takes the entire range (B3:B13) and creates a subset out of it for all the cells where ISTEXT has the value TRUE, effectively only taking rows where cells contain a text value.
Finally, you use the COUNTA function smartly to return the count of 7.
That function is also important here because the function returns the count of all non-empty cells and doesn’t work.
When you want the count of cells containing values of a specific type, that’s where the subset created by the FILTER function is the game-changer.
Even though the method is straightforward to understand and unique in its own way, it might slow the application down when working on a sufficiently large dataset.
For example, imagine you have a dataset of 1 million rows. The ISTEXT function will first check for each cell whether its value is text or not. It then uses that output as filtering criteria inside the FILTER function to slice the data down to a subset of only text values. And then finally returning the count of cells from that subset 😥.
However, the good news is, most of the time, you will not come across a dataset with millions of rows where you only need to find the count of text values.
Using ARRAYFORMULA and ISTEXT with Others
The next in line is using the ARRAYFORMULA in combination with the ISTEXT to produce the result.
The method doesn’t really care whether your dataset is enormous or a shorter one. It works with the same efficiency, and at the core of it is the ARRAYFORMULA function😉.
I am using a column containing mixed types of values while explaining this method as below.
To return the count of only those cells containing text values, use the following formula in cell E3. And you will see the output as shown below.
=ARRAYFORMULA(SUM(IF(ISTEXT(B3:B13),1,0)))
Just to make you aware of why the ARRAYFORMULA is so crucial here, I am breaking the formula down into the pieces.
1. Imagine we use the IF function to return 1 whenever the ISTEXT returns TRUE for each cell of the range B3:B13. It would be something like below.
2. Now, you know that if you sum up all these cells, you will get the count of all those cells that only contain text value.
However, this task is redundant, and you don’t want to do it. You need someone that works very well with the arrays and allows you to encapsulate this two-step workaround into a single one.
That’s where the ARRAYFORMULA comes into the picture with its ability to work well with the operations as well as its ability to provide other non-array formulas like ISTEXT the power to work with arrays.
You know what? You can further simplify this formula by removing the IF condition and using the double unary(–) instead of it.
The double unary (–) will convert the boolean TRUE/FALSE array into an array of numeric 1’s and 0’s. The rest everything of this formula stays the same.
=ARRAYFORMULA(SUM(--ISTEXT(B3:B13)))
That’s another way to tweak the above formula without compromising the output.
Count Cells with a Specific Text
Sometimes, you might be interested in finding out the count of cells with a specific text. For example, only the cells where the name John appears? It is very crucial when you are analyzing the data.
Can you count cells with a specific text in Google Sheets? Hell Yes! You can!
Through this section, I will show you how!
The dataset I will use for this demo differs from the one used in previous demos and is shown below.
Let’s assume you want to know how many times the name “Mary Johnson” appears in the list shown in the screenshot above.
The generic COUNTIF formula to get the count of cells containing a specific text is as below:
=COUNTIF(range, “text”)
Use the following COUNTIF formula inside cell E3 to get the count of cells containing the name “Mary Johnson.”
=COUNTIF(B3:B13, "Mary Johnson")
The COUNTIF function runs through all the cells in range B3:B13 and checks whether the criteria (name = Mary Johnson) is fulfilled by each cell. Wherever it is fulfilled, the count of all those cells is returned.
Count cells with a Partial Text Match
Once you know how to count cells containing specific text, it is evident that the next important thing is to count how many times a partial text appears inside the given range.
Imagine you are doing exploratory analysis on the dataset provided, and you are interested to know how many times the cells contain a partial text “Turnor.” You can find it out in Google Sheets.
Again, the formulas will not differ, but only the logic will be tweaked a bit here.
The dataset I will use for this demo is as shown below.
In cell E3, use the formula below to return the count of cells where all the names with “Turner” are placed.
=COUNTIF(B3:B13, "*Turner*")
The wildcard asterisk is the key here. Its use before and after the text “Turner” suggests that whatever comes before and after that text should be counted.
And with that, I mark the end of this article where you learned five fabulous ways to count cells containing only text values.
The COUNTIF method is the simplest of them all and easy to remember.
The SUMPRODUCT and ISTEXT method becomes unique by converting an array of TRUE FALSE to an array of respective numeric values.
The COUNTA, FILTER method is unique in a way that it subsets the original data only to show and count the one where the ISTEXT formula returns true.
The ARRAYFORMULA and ISTEXT, in combination with SUM and IF conditions, introduce you to the power of arrays while counting.
And finally, you come across two scenarios where you count cells containing text based on a specific text and based on a partial text match.
Other articles you may also like: