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


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.

Excel VBA to Count Cells Containing Specific Text

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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo