Count Cells With Any Text – Excel & Google Sheets
Written by
Reviewed by
Download the example workbook
In this tutorial, we will use the COUNTIF and SUMPRODUCT functions to count cells containing any text within a range.
COUNTIF To Count Cells With Any Text
The COUNTIF function counts cells that meet certain criteria. In this case, we want to count cells that contain any text. We do this by using an asterisk wildcard symbol:
=COUNTIF(B2:B6,"*")
The asterisk wildcard matches any string of text of any length. If you have no characters before or after the asterisk (as in our example above), it will match any and all text.
Remember when entering text strings into formulas, you must always surround the strings with quotations marks. This is true even of wildcards.
COUNTIFS Function to Count Cells with Conditions
The COUNTIFS function works the same as the COUNTIF function, except it allows for multiple criteria. To demonstrate an example, let’s use the same example as above except this time we will exclude cells that say “ABC”. We can do this by adding the “<>” symbol as a conditional statement which represents “not equal to”.
=COUNTIFS(Range,"*", Range,"<>Text")=COUNTIFS(B2:B6,"*", B2:B6, "<>ABC")
You will notice that even in this formula, the condition is surrounded by quotation marks. You can add as many conditions as you require, just remember to follow the function’s formula structure by adding the range and then the condition.
It should be noted that “<>ABC” is not case sensitive so it will also include cells that are lower case, i.e. cells with “abc”, “ABC”, “aBc” will all be included.
SUMPRODUCT To Count Cells With Any Text
The SUMPRODUCT function can also be used to conditionally count cells. It’s more complicated than the COUNTIF function, but is much more powerful.
=SUMPRODUCT(INT(ISTEXT(Range))=SUMPRODUCT(INT(ISTEXT(B2:B6))
Let’s break down this formula to understand each part of it
Highlight a portion of the function in the formula bar and press F9 to see the calculated value of that formula portion.
ISTEXT is a Boolean function that returns TRUE or FALSE based on whether a cell contains text. However, when used inside a SUMPRODUCT Function it will return an array of TRUE/FALSE values: {TRUE; FALSE; FALSE; TRUE; TRUE}.
Next we use the INT Function to convert the Boolean values to 1s and 0s so that the SUMPRODUCT function can sum the values, counting the number of cells containing text.
You can also use the double unary sign “–” (also commonly referred to as the double negative) which converts TRUE and FALSE values into 1s and 0s respectively.
Last, SUMPRODUCT will take the summation of that array: 1 + 0 + 0 + 1 + 1 = 3.
Google Sheets – Count Cells With Any Text
All of the above examples work exactly the same in Google Sheets as in Excel.
