Count cells that contain specific text

countif-is-non-case-sensitive

To count cells that contain specific text in Excel, you can use the COUNTTEXT, COUNTIF, or SUMPRODUCT functions.

How to count cells that contain specific text

Steps to count cells that contain specific text using COUNTIF:

  1. Open Excel
  2. Type =COUNTIF(range,”*X*“)
  3. Press Enter.
  4. The formula counts cells that contain a specific substring.

Explanation: If you are unfamiliar with user-defined functions, you can use regular Excel functions to count cells that contain specific text. However, this workaround needs attention; we will use a wildcard to create a formula.

countif-is-non-case-sensitive

In the example, use the formula in cell F3:

=COUNTIF(data,”X”)

The result is 5. The main problem with COUNTIF is that you can not control the case-sensitive search; the function always uses a non-case-sensitive search.

SUMPRODUCT Function

Follow the steps to count cells containing a specific text using SUMPRODUCT:

  1. Open Excel
  2. Type =SUMPRODUCT(–ISNUMBER(FIND(“X”,data)))
  3. Press Enter.
  4. The formula counts cells that contain a specific text.

Explanation: This example will show you a workaround with the SUMPRODUCT function using the boolean logic. The solution will provide a case-sensitive search (finally!).

count-cells-that-contain-specific-text-sumproduct-workaround

Evaluate the formula from the inside out. The FIND function is case-sensitive so that the function will identify “X” and “x” as different values. The formula uses a logical test.

=FIND(“X”,data)

Use the FIND function because it gets the position of text in a text string as a number. In the example, the function returns 9 since the “X” character is in the 9th position of the given text.

=FIND(“x”,”001-799-XXX-01” = 9

If the “X” character is not found in the text, the function returns a #VALUE error. Next, the ISNUMBER function will check whether the cell contains a numeric value.

=ISNUMBER(FIND(“x”,data)

This formula returns an array containing TRUE and FALSE, or boolean values. To convert boolean values to 0s and 1s, use the double-negative method.

=–(ISNUMBER(FIND(“x”,data))
= {1, 0, 1, 0, 1, 0, 0, 0, 0}

Finally, the SUMPRODUCT function sums the matching values in the array, and the result is 3.

=SUMPRODUCT({1, 0, 1, 0, 1, 0, 0, 0, 0}) = 3.

For a non-case-sensitive count, use the FIND function in the following formula:

=SUMPRODUCT(–ISNUMBER(SEARCH(“X”,data)))

COUNTTEXT Function

COUNTTEXT is a powerful user-defined function and a part of our free add-in library, DataFX. The goal is to count cells that contain one or more strings in the given cell. The main advantage of using the function is that you do not need to apply wildcards or create complex formulas.

Okay, let’s count cells containing specific text:

  1. Open Excel
  2. Type the =COUNTTEXT(B3:B11,”X”) formula.
  3. Press Enter.
  4. The formula returns the number of cells that contain a specific text.

In the example, the data is in the range B3:B11, which contains text values. For the sake of simplicity, create a named range; select it and add a descriptive name using the name box. From now on, “data” refers to B3:B11.

Formula:

=COUNTTEXT(data,”X”)

The function uses case-sensitive search by default, so the result is 3. In cell F4, we are using the third optional argument for a non-case-sensitive search (where the text contains “X” or “x” values). Therefore, the third argument is set to FALSE.

count-cells-that-contain-specific-text-excel

=COUNTTEXT(data,”X”, FALSE)

In this case, the result is 5; COUNTTEXT counts the cells that contain “x” or “X”.