COUNTTEXT function

Use the Excel COUNTTEXT function to count cells that contain one or more text values instead of using the built-in COUNTIF function.




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)


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:

count multiple substrings

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”).


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:


The 3rd argument is FALSE, so the formula returns 4. To use a non-case sensitive search, change the formula to:


Related Formulas

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.