Use the Excel COUNTTEXT function to count cells that contain one or more text values instead of using the built-in COUNTIF function.
Syntax
=COUNTTEXT(rng,str,caseSensitive)Arguments
The function uses two required and one optional argument:
- “rng”: the range of cells where you want to count cells that contain a specific text
- “str”: the list of strings that you want to find in the original string
- “caseSensitive”: optional argument; with its help, you can control the search type (TRUE or FALSE)
COUNTTEXT example
In the example, we have the following data set in range B3:B6.
If the optional argument is TRUE or not specified, the search is case-sensitive; if FALSE, the function uses a noncase-sensitive search. The goal is to count cells that contain the substring “A” using a case-sensitive search. Configure the function argument:
- range: B3:B6
- str: “A”
- caseSensitive: FALSE
The formula in cell D3:
=COUNTTEXT(B3:B6,"A",FALSE)
The formula returns 4 since all four cells contain “A” or “a” characters.
We use a case-sensitive search in the next example, so change the last argument to TRUE. Again, the goal is to count the number of cells that contain “a” (not “A”).
=COUNTTEXT(B3:B6,"a",TRUE)The result is 3 since only the first three cells contain “a”.
Count cells that contain multiple substrings
The biggest advantage of using the COUNTTEXT function is that we can add multiple criteria. For example, if you want to count cells that contain “M” or “L” characters, use the following formula in cell D5:
=COUNTTEXT(B3:B6,"M,L",FALSE)The 3rd argument is FALSE, so the formula returns 4. To use a non-case sensitive search, change the formula to:
=COUNTTEXT(B3:B6,"M,L",TRUE)