Explanation
In this example, the goal is to count cells that contain a specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version can also perform a case-sensitive count.
COUNTIF function
The COUNTIF function counts cells in a range that meet supplied criteria. For example, to count the number of cells in a range that contain “apple” you can use COUNTIF like this:
=COUNTIF(range,"apple") // equal to "apple"
Notice this is an exact match. To be included in the count, a cell must contain “apple” and only “apple”. If a cell contains any other characters, it will not be counted.
In the example shown, the goal is to count cells that contain specific text, meaning the text is a substring that can be anywhere in the cell. To do this, we need to use the asterisk (*) character as a wildcard . To count cells that contain the substring “apple”, we can use a formula like this:
=COUNTIF(range,"*apple*")
The asterisk (*) wildcard matches zero or more characters of any kind, so this formula will count cells that contain “apple” anywhere in the cell. The formulas used in the worksheet shown follow the same pattern:
=COUNTIF(B5:B15,"*a*") // contains "a"
=COUNTIF(B5:B15,"*2*") // contains "2"
=COUNTIF(B5:B15,"*-S*") // contains "-s"
=COUNTIF(B5:B15,"*x*") // contains "x"
You can easily adjust this formula to use a cell reference in criteria . For example, if A1 contains the text you want to match, you can use:
=COUNTIF(range,"*"&A1&"*")
Inside COUNTIF, the two asterisks are concatenated to the value in A1, and the formula works as before. The COUNTIF function supports three different wildcards, see this page for more details.
Note the COUNTIF formula above won’t work if you are looking for a particular number and cells contain numeric data. This is because the wildcard automatically causes COUNTIF to look for text only (i.e. to look for “2” instead of just 2). Because a text value won’t ever be found in a true number, COUNTIF will return zero. In addition, COUNTIF is not case-sensitive, so you can’t perform a case-sensitive count. The SUMPRODUCT alternative below can handle both cases.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and Boolean algebra . This approach has the benefit of being case-sensitive if needed. In addition, you can use this technique to find a number inside of a number, something you can’t do with COUNTIF.
To count cells that contain specific text with SUMPRODUCT, you can use the SEARCH function . SEARCH returns the position of text in a text string as a number. For example, the formula below returns 6 since the “a” appears first as the sixth character in the string:
=SEARCH( "a","The cat sat") // returns 6
If the text is not found, SEARCH returns a #VALUE! error:
=SEARCH( "x","The cat sat") // returns #VALUE!
To count cells that contain “a” in the worksheet shown with SUMPRODUCT, you can use the ISNUMBER and SEARCH functions like this:
=SUMPRODUCT(--ISNUMBER(SEARCH("a",B5:B15)))
Working from the inside out, the logical test inside SUMPRODUCT is based on SEARCH:
SEARCH("a",B5:B15)
Because the range B5:B15 contains 11 cells, the result from SEARCH is an array with 11 results:
{1;1;1;1;2;2;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
In this array, numbers indicate the position of “a” in cells where “a” is found. The #VALUE! errors indicate cells where “a” was not found. To convert these results into a simple array of TRUE and FALSE values, the SEARCH function is nested in the ISNUMBER function :
ISNUMBER(SEARCH("a",B5:B15))
ISNUMBER returns TRUE for any number and FALSE for errors. SEARCH delivers the array of results to ISNUMBER, and ISNUMBER converts the results to an array that contains only TRUE and FALSE values:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
In this array, TRUE corresponds to cells that contain “a” and FALSE corresponds to cells that do not contain “a”. We want to count these results, but we first need to convert the TRUE and FALSE values to their numeric equivalents, 1 and 0. To do this, we use a double negative (–):
--ISNUMBER(SEARCH("a",B5:B15))
The result inside of SUMPRODUCT looks like this:
=SUMPRODUCT({1;1;1;1;1;1;0;0;0;0;0}) // returns 6
With a single array to process, SUMPRODUCT sums the array and returns 6 as a final result.
One benefit of this formula is it will find a number inside a numeric value. In addition, there is no need to use wildcards to indicate position, because SEARCH will automatically look through all text in a cell.
Case-sensitive option
For a case-sensitive count, you can replace the SEARCH function with the FIND function like this:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
The FIND function works just like the SEARCH function, but is case-sensitive. You can use a formula like this to count cells that contain “APPLE” and not “apple”. This example provides more detail .
Explanation
In this example, the goal is to count cells in a range that contain text values. This could be hard-coded text like “apple” or “red”, numbers entered as text, or formulas that return text values. Empty cells and cells that contain numeric values or errors should not be included in the count. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both approaches are explained below. For convenience, data is the named range B5:B15.
COUNTIF function
The simplest way to solve this problem is with the COUNTIF function and the asterisk () wildcard . The asterisk () matches zero or more characters of any kind. For example, to count cells in a range that begin with “a”, you can use COUNTIF like this:
=COUNTIF(range,"a*") // begins with "a"
In this example, however, we don’t want to match any specific text value. We want to match all text values. To do this, we provide the asterisk (*) by itself for criteria. The formula in H5 is:
=COUNTIF(data,"*") // any text value
The result is 4 because there are four cells in data (B5:B15) that contain text values.
To reverse the operation of the formula and count all cells that do not contain text, add the not equal to (<>) logical operator like this:
=COUNTIF(data,"<>*") // non-text values
This is the formula used in cell H6. The result is 7, since there are seven cells in data (B5:B15) that do not contain text values.
COUNTIFS function
To apply more specific criteria, you can switch to the COUNTIFS function , which supports multiple conditions. For example, to count cells with text, but exclude cells that contain only a space character, you can use a formula like this:
=COUNTIFS(range,"*",range,"<> ")
This formula will count cells that contain any text value except a single space (" “).
SUMPRODUCT function
Another way to solve this problem is to use the SUMPRODUCT function with the ISTEXT function . SUMPRODUCT makes it easy to perform a logical test on a range, and then count the results. The test is performed with the ISTEXT function. True to its name, the ISTEXT function only returns TRUE when given a text value:
=ISTEXT("apple")// returns TRUE
=ISTEXT(70) // returns FALSE
To count cells with text values in the example shown, you can use a formula like this:
=SUMPRODUCT(--ISTEXT(data))
Working from the inside out, the logical test is based on the ISTEXT function:
ISTEXT(data)
Because data (B5:B15) contains 11 values, ISTEXT returns 11 results in an array like this:
{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
In this array, the TRUE values correspond to cells that contain text values, and the FALSE values represent cells that do not contain text. To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (–):
--{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({1;1;1;0;0;1;0;0;0;0;0}) // returns 4
With a single array to process, SUMPRODUCT sums the array and returns 4 as the result.
To reverse the formula and count all cells that do not contain text, you can nest the ISTEXT function inside the NOT function like this:
=SUMPRODUCT(--NOT(ISTEXT(data)))
The NOT function reverses the results from ISTEXT. The double negative (–) converts the array to numbers, and the array inside SUMPRODUCT looks like this:
=SUMPRODUCT({0;0;0;1;1;0;1;1;1;1;1}) // returns 7
The result is 7, since there are seven cells in data (B5:B15) that do not contain text values.
Note: the SUMPRODUCT formulas above may seem complex, but u sing Boolean operations in array formulas is powerful and flexible. It is also a n important skill in modern functions like FILTER and XLOOKUP , which often use this technique to select the right data. The syntax used by COUNTIF on the other hand is unique to a group of eight functions and is therefore not as useful or portable.
