Table of Contents
In Excel, you can count the number of cells that contain specific text by using the COUNTIF function. This function takes two arguments, the range of cells to count and the criteria to match. The criteria argument is a text string enclosed in quotation marks or a cell reference that contains the text you are looking for. The COUNTIF function then counts the number of cells that match the criteria and returns the result. The result can be displayed in a cell or used in a formula.
You can use the following methods to count cells in Excel that contain specific text:
Method 1: Count Cells that Contain One Specific Text
=COUNTIF(A2:A13, "*text*")
This formula will count the number of cells in the range A2:A13 that contain “text” in the cell.
Method 2: Count Cells that Contain One of Several Text
=SUM(COUNTIF(A2:A13,{"*text1","*text2*","*text3*"}))
This formula will count the number of cells in the range A2:A13 that contain “text1”, “text2”, or “text3” in the cell.
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Count Cells that Contain One Specific Text
We can use the following formula to count the values in the Team column that contain “avs” in the name:
=COUNTIF(A2:A13, "*avs*")
The following screenshot shows how to use this formula in practice:
We can see that a total of 4 cells in the Team column contain “avs” in the name.
Example 2: Count Cells that Contain One of Several Text
We can use the following formula to count the number of cells in the Team column that contain “avs”, “urs”, or “ockets” in the name:
=SUM(COUNTIF(A2:A13,{"*avs","*urs*","*ockets*"}))
The following screenshot shows how to use this formula in practice:
We can see that a total of 7 cells in the Team column contain “avs”, “urs”, or “ockets” in the name.