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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Count Cells with Specific Text in Excel: 5 Easy Ways

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.

Excel Count Cells with Specific Text


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*")

Count Cells with Partially Matched Specific Text

  • 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")

Count Cells with Exact Matching Text in Excel

  • 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)

Read More: How to Apply COUNTIF When Cell Contains Specific Text


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.

Count Specific Text Cells with Data Validation in Excel

  • 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))))

Apply SUMPRODUCT Function for Counting Cells with Specific Text

  • 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.

Excel VBA to Count Cells with Specific Text

  • 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.

Read More: Excel VBA to Count Cells Containing Specific Text


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.

Apply Filter for Counting Specific Text Cells in Excel

  • Afterward, filter your dataset according to your preference.
  • 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.

Read More: How to Count Filtered Cells with Text in Excel


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.

Count Cells with All Types of Text in Excel

  • 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)))

Read More: How to Count Cells in Excel with Different Text


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.

Count Cells with Multiple Substrings in Excel

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&"*")


Download Practice Workbook

Download this sample workbook and practice by yourself.


Conclusion

In this article, you can see different formulas that can count cells with specific text in excel. Hope you find this article interesting. This article will help you to find specific data for your worksheet. Stay tuned for more articles.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
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.

4 Comments
  1. very intresting account always thought there was a way to handle the need. If I can here’s a question I have tried to have the sheet highlight data in cells but every time I it doesn’t work out. Can you possibly cover that if you haven’t yet. Thanks in advance

  2. I was able to add conditional formatting by using the same formula. Where A2 used a formula to determine which cells to format, and the formula → =COUNTIF(A3:A3,”*Apple*”)=1
    Then copied this to the column.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo