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.

Download Practice Workbook

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

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.

Read More: COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)

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.

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. You can also visit our Exceldemy blog to learn more about excel. Stay with us and keep learning.

Related Articles

• Count If a Cell Contains Any Text in Excel (4 Methods)
• How to Count Specific Words in a Column in Excel (2 Methods)
• How to Count Frequency of Text in Excel (Using VBA)!
• Count If Cell Contains Text in Excel (5 Easy Approaches)
• How to Count Filtered Cells with Text in Excel (3 Methods)
• How to Count Cells in Excel with Different Text (5 Ways)