Excel VBA to Count Cells Containing Specific Text

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

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.

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