Count cells that contain specific text

Count cells that contain specific text

Generic formula 

=COUNTIF(rng,"*txt*")

Related formulas 

Count cells that contain text

Cell contains specific text

Running count of occurrence in list

Summary count with COUNTIF

Count cells that contain either x or y

If cell contains one of many things

Explanation

To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*" is a wildcard matching any number of characters.

In the example, the active cell contains this formula:

=COUNTIF(B4:B11,"*a*")

How this formula works

COUNTIF counts the number of cells in the range that contain "a" by matching the content of each cell against the pattern "*a*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any number of characters", so this pattern will count any cell that contains "a" in any position. The count of cells that match this pattern is returned as a number.

You can easily adjust this formula to use the contents of another cell for the criteria. For example, if A1 contains the text you want to match, use the formula:

=COUNTIF(rng,"*"&a1&"*")

Case-sensitive version

If you need a case-sensitive version, you can't use COUNTIF. Instead you can test each cell in the range using a formula based on the FIND function and the ISNUMBER function, as explained here.

FIND is case-sensitive, and you'll need to give it the range of cells and then use SUMPRODUCT to count the results. The formula looks like this:

=SUMPRODUCT(--(ISNUMBER(FIND(text,rng))))

Where text is the text you are looking for, and rng is the range of cells you want to count. There's no need to use wildcards, because FIND will return a number if text is found anywhere in the cell.

Count cells that contain specific text

Generic formula 

=COUNTIF(rng,"*txt*")

Related formulas 

Count cells that contain text

Cell contains specific text

Running count of occurrence in list

Summary count with COUNTIF

Count cells that contain either x or y

If cell contains one of many things

Explanation

To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*" is a wildcard matching any number of characters.

In the example, the active cell contains this formula:

=COUNTIF(B4:B11,"*a*")

How this formula works

COUNTIF counts the number of cells in the range that contain "a" by matching the content of each cell against the pattern "*a*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any number of characters", so this pattern will count any cell that contains "a" in any position. The count of cells that match this pattern is returned as a number.

You can easily adjust this formula to use the contents of another cell for the criteria. For example, if A1 contains the text you want to match, use the formula:

=COUNTIF(rng,"*"&a1&"*")

Case-sensitive version

If you need a case-sensitive version, you can't use COUNTIF. Instead you can test each cell in the range using a formula based on the FIND function and the ISNUMBER function, as explained here.

FIND is case-sensitive, and you'll need to give it the range of cells and then use SUMPRODUCT to count the results. The formula looks like this:

=SUMPRODUCT(--(ISNUMBER(FIND(text,rng))))

Where text is the text you are looking for, and rng is the range of cells you want to count. There's no need to use wildcards, because FIND will return a number if text is found anywhere in the cell.