Excel VBA to Count Cells Containing Specific Text

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to know how many times a certain text appears within a range of data in excel. For example, consider a dataset containing dates and customer names. You need to count the cells for a particular customer name to know how many times s/he appeared in your shop within a certain period of time. Obviously, you cannot manually do that with a large set of data. You can use Excel VBA to count cells containing specific text. This article will help you to do that easily. The following picture highlights the purpose of this article.

Excel VBA to Count Cells Containing Specific Text


Excel VBA to Count Cells Containing Specific Text

Suppose you have the following dataset. It contains a list of names and their first 3 fruit choices. Now, you want to enter a fruit name in cell G5. And then, in cell H5, you want to find out how many times that fruit was chosen in their first 3 choices.

Then, follow the steps below.

Steps

  • First, press ALT+F11 (on Windows) or Opt+F11 (on Mac) to open the Microsoft Visual Basic for Applications (VBA) window. You can open it from the Developer tab also.
  • Then, select Insert >> Module.

  • After that, copy the following code.
Sub CountCellsWithSpecificText()

Range("H5") = Application.WorksheetFunction.CountIf(Range("C5:E17"), Range("G5"))

MsgBox Range("G5") & " was chosen " & Range("H5") & " times."

End Sub
  • Next, paste the copied code on the blank module as follows.

  • Then, run the code by clicking on the Run icon or using the Run tab as shown in the following picture.

  • Finally, you will get the following result.

Excel VBA to Count Cells Containing Specific Text


Things to Remember

  • You can change the cell ranges in the code according to your dataset.
  • Delete the MsgBox code-line if you don’t want a message box to be shown.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to use Excel VBA to count cells containing specific text. Please use the comment section below if you have any more queries or suggestions. Stay with us and keep learning.


<< Go Back to With Text | Count Cells | Formula List | Learn Excel

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.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo