Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Count Cells with Specific Text in Excel (5 Easy Ways)

While working with an extensive range of data, finding the number of a specific text is often required. Microsoft Excel has empowered us to count cells with specific text with some easy ways. In this article, we will see how to do that.

## 5 Easy Ways to Count Cells with Specific Text in Excel

To describe the process, let`s say we have a dataset here with information on fruit names and their amounts. Here, I want to find the number of cells that have the name Apple in them.

Now, we will follow the 6 easy ways below and try to count cells with that specific text.

### 1. Count Cells with Specific Text Using COUNTIF Function

#### 1.1 Partially Matched

In this first process, we will count the cells that have the text Apple with the COUNTIF function. Let’s follow the process below:

• First, insert this formula in cell C17.
`=COUNTIF(B5:B15,"*Apple*")`

• After this, press Enter.
• Finally, you will see the number of cells that contain the text Apple.
• It also extracted text from Pineapple because it has the same text as well.

Here, we used the COUNTIF function to count cell range B5:B15 to meet the single condition of finding the text Apple. Along with it, we inserted the Wildcard (*) for partial matching.

Note: You can also use this formula in this context.

`=COUNTIF(B5:B15,"*"&B5&"*")`

#### 1.2 Exact Matched

Following this second process, we will count cells that have the exact matching text in the dataset. We will again use the COUNTIF function for this purpose. Check the process below:

• First, insert this function in cell C17.
`=COUNTIF(B5:B15,"Apple")`

• After this,  hit Enter.
• That’s it, we have got the number of cells that only contain the text Apple.

Note: Instead of typing specific text, you can simply assign a reference cell in this formula.

`=COUNTIF(B5:B15,B5)`

### 2. Apply Data Validation to Count Specific Text Cells in Excel

At this stage, we will use the Data Validation tool to count cells that have specific text. Let’s see how it works.

• In the beginning, select cell F9 because we will insert the Data Validation command here.
• Then, go to the Data tab and select Data Validation under the Data Tools group.

• Next, select List for Allow group and cell range B5:B15 in the Source box.

• After this, press OK.
• Following, you will see an arrow beside cell F9.
• Now, select specific text from the drop-down list.

• Following, insert this formula in cell F10.
`=COUNTIF(B5:B15,F9)`

• Lastly, hit Enter to see the final output.

### 3. Insert SUMPRODUCT Function for Counting Cells with Specific Text

This fourth method will guide you on counting cells with specific text by applying the SUMPRODUCT function. Let’s check the steps below:

• First, insert this formula in cell C17.
`=SUMPRODUCT(--(ISNUMBER(FIND("Apple",B5:B15))))`

• Thereafter, hit Enter.
• That’s it, you got the cell number containing that specific text.

Initially, we applied the SUMPRODUCT function to return the products’ sum from cell range B5:B15.  Following, we used the ISNUMBER function to check if the reference value “Apple” is a number or not. Lastly, we applied the FIND function to search for the specific text from the data string.

Note: You can also apply this formula instead.

`=SUMPRODUCT(--(ISNUMBER(FIND(B5,B5:B15))))`

### 4. Count Cells with Specific Text Using Excel VBA

We can use Excel VBA code for counting cells of specific text. To perform this, simply follow the steps below:

• First, click on Visual Basic under the Developer tab.

• Following, select Module from the Insert section.

• Now, insert this code on the blank page.
``````Sub COUNT_TEXT()
Dim iVal As Integer
iVal = Application.WorksheetFunction.CountIf(Range("B5:B15"), "Apple")
MsgBox iVal
End Sub
<img class="aligncenter wp-image-224403 size-full" src="https://www.exceldemy.com/wp-content/uploads/2018/06/Excel-Count-Cells-with-Specific-Text-15-1.png" alt="Excel VBA to Count Cells with Specific Text" width="732" height="210" />``````

Here you can edit the range and specific text to perform in your worksheet.

• After this, click on Run Sub or press F5 on your keyboard.

• Then, click on Run in the Macros window.

• Lastly, you will see the number of cells in a dialogue box like this:

Additional Tip: You can use the below code if you want to count your specific text row-wise.
``````Sub COUNT_ROWS()
For j = 1 To 12  'Adjust Rows
count = 0
For i = 1 To 2 Step 12 'Adjust Columns
If Cells(j, i) = "Apple" Then count = count + 1
Next i
MsgBox "Count of 'Apple's in row " & j & " is " & vbLf & count
Next j
End Sub``````

After running the code, you will see that a text box appears which will tell you the number of times your text appears in the 1st row. After pressing Ok, it will tell you the number of appearances of that specific text in the 2nd row. It will continue to appear in a total of 12 times.

### 5. Specific Text Cells Counting with Filter Option in Excel

In this last method, we will apply the Filter to count cells for a specific text. Thereafter, we will apply the SUMPRODUCT function. Let’s see how it works.

• First, select cell range B4:C15.
• Then, go to the Data tab and select Filter from the Sort & Filter group.

• Now, insert this formula in cell 18.
`=SUMPRODUCT(SUBTOTAL(103, INDIRECT("C"&ROW(C5:C15))), --(B5:B15=C17))`

• Lastly, press Enter and you will see the final output.

Here, we applied the SUMPRODUCT function to get the sum of the dataset. Then, applied the SUBTOTAL function for getting total cumulative data. After this, we provided the INDIRECT function to lock cell C17. Finally, used the ROW function to select the rows from the dataset.

## How to Count Cells with All Types of Text in Excel

So far we counted cells that have a specific text. Let us now find out the number of cells comprising all types of texts. Following is the procedure:

• First, insert this formula in cell C17.

• After this, hit Enter.
• Therefore, you can see the cell numbers from the cell range B5:C15.

Note: Instead of this one, you can apply this formula as well.

`=SUMPRODUCT(--(ISTEXT(B5:C15)))`

## How to Count Cells with Multiple Substrings in Excel

Let’s say you want to calculate the number of cells filling more than one text criterion. In this example, we have a list where different items are listed along with their color and size.

We want to count the item T-Shirt with the color blue. For this, we will apply the COUNTIFS function.

• First, insert this formula in cell E8.
`=COUNTIFS(B5:B12,"*T-Shirt*", B5:B12,"*Blue*")`

• Afterward, hit Enter and you will see the final output.

Note: Apply this formula instead with a cell reference.

`=COUNTIFS(B5:B12,"*"&E6&"*", B5:B12,"*"&E7&"*")`

## Related Articles

#### Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.