Count Frequency of Text in Excel (4 Effective Ways)

Have you ever wondered how to count the frequency of text in Excel, i.e. the number of times a specific text appears in a range? In this article, we are going to show you how to count the frequency of text in Excel using functions, a pivot table, and a suitable VBA code. The following image will give you a brief idea of what we are actually going to perform here.

count frequency of text in excel


Download Practice Workbook

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


4 Ways to Count Frequency of Text in Excel

Let’s assume that a hypothetical tennis coach is evaluating the grips of the different players, registered at the tennis clinics he runs. He wants to input a certain type of grip and see how many players at the clinic use that particular grip.

The source data is shown below:

source data to count frequency of text

Follow the methods below to learn how you can do that for him.


1. Count Frequency of Text with COUNTIF Function

You can easily count the frequency of a text using the COUNTIF function in excel.

📌 Steps:

  • Just enter the preferred text cell F5 and then apply the following formula in cell G5.
=COUNTIF(C5:C99,F5)

COUNTIF function to count frequency of text


2. Count Frequency of Text Using SUM and IF Functions

Alternatively, you can create a combo formula using the SUM and IF functions in excel to achieve that.

📌 Steps:

  • Apply the following formula in cell G5 instead to do that.
=SUM(IF(C5:C99=F5,1,0))

Use SUM & IF functions to count text frequency


3. Count Frequency of Text Using Excel PivotTable

Follow the steps below to create a PivotTable to quickly get the frequency of a text in excel.

📌 Steps:

  • First, click anywhere in the dataset or select it entirely.
  • Then select Insert >> PivotTable.
  • Next, make sure that the detected range is accurate.
  • After that, enter the destination range for the Pivot Table. Then click OK.

Create a PivotTable in Excel

  • Now drag the Preferred Grip field to the Rows and Values area in the PivotTable Selection Pane.

Use PivotTable Selection Pane to add fields

  • After that, you will get the desired result as follows.

Count text frequency using PivotTable


4. Count Frequency of Text Using Excel VBA

Follow the steps below to apply VBA to count the frequency of specific grips.

📌 Steps:

  • First things first, we will create a named range to encompass all the grips that have been added already for each player in the Preferred Grip column.
  • So, select the cell range C5:C99, type Grips in the Name box, which is located at the left end of the formula bar, and press Enter.

create a named range

  • Now, go to Developer >> Code >> Visual Basic in order to access the Visual Basic Editor (VBE). Or, just press Alt+F11. Then go to Insert >>> UserForm.

Insert a UserForm in VBA

  • Next, select View >> Properties Window in order to see the Properties window.

select View>>Properties Window

  • Now, using the Properties window, change the name of the UserForm to frmTextFrequency, the back color to light yellow, and the caption to “See the frequency of specified text”.

Change the Userform properties

  • Then, insert a CommandButton on the form and using the Properties window, change the name of the button to cmdClickToSeeInputBox, the BackStyle to 0 – fmBackStyleTransparent, and the caption to “Click to enter the grip type you want to check the frequency of”.

insert a command button to the UserForm

  • Next, insert a TextBox below the button. Then, using the Properties Window change the name of the textbox to txtResult and set the Visible property to False.

insert a TextBox to the UserForm

  • After that, right-click the button and select View Code as shown.

On CommandButton: Right-Click>>View Code

  • Enter the following code for the button click event:
Dim criteriaOne As String 
Dim rng As Range
Dim countOne As Integer
Dim resultOne As String
criteriaOne = InputBox("Choose the grip, you want to see the frequency of", "Frequency Count")
Set rng = Worksheets("VBA").Range("Grips")
countOne = Application.WorksheetFunction.CountIf(rng, criteriaOne)
resultOne = "mentioned " & countOne & " times"
txtResult.Value = resultOne
txtResult.Visible = True
Note:
We want to ultimately use the COUNTIF function in VBA, in order to tell us the frequency of a certain text. We have declared the variables first in the code. The criteria will be obtained from user input in an input box and the range has been set as the named range of the workbook, which we named earlier. The text box appears with the results after the user enters the input. The visibility of the textbox is set to true again since we previously set it to false. Usually, code like this requires error handling (since we are getting input from the user) but in this case, the COUNTIF function will simply deliver 0 results if the user enters a grip that doesn’t exist or spells the name of the grip wrong.
  • We will now insert another CommandButton on the form to close the UserForm, and using the Properties Window, change the name of the button to cmdCloseForm, the BackColor to red, the Caption to Close the Form, and the ForeColor to white (this changes the font color to white).

Add CommandButton to close UserForm

  • Then, right-click the newly added button, choose View Code, and enter the following code for the button click event:
Unload Me

This allows the user to close the form using this button.

  • Next, go back to the Worksheet and select Developer >> Controls >> Insert ActiveX Controls >> CommandButton to insert a form viewer button on the sheet.

Add an Active X Control CommandButton on worksheet

  • After that, change the name of the button to cmdForm and the caption to “Check the Frequency of Text Form”, while in Design mode.

change the Name and Caption of the form viewer button

  • Then right-click the newly created button on the worksheet, choose View Code, and, enter the following code for the button click event:
frmTextFrequency.Show
  • Next, return to the worksheet, make sure Design mode is unchecked, and, click on the button to show the form.

Click on the form viewer button

  • The textbox is not shown initially since its visibility was set to False. Now click on the “Click to enter the type of grip you want to see the frequency of” button and an InputBox should appear as shown below.

click to check frequency

  • Then type Continental Grip in order to see the number of times Continental Grip appears in the range and then click Ok.

enter text to count it's frequency

  • After that, the textbox appears stating the number of times Continental Grip is mentioned in the range.

TextBox showng the count of frequency of the text

  • Finally, close the form using the red button, and don’t forget to save your workbook as a macro-enabled workbook.

And there you have it.

Related Content: How to Count Specific Words in a Column in Excel (2 Methods)


Things to Remember

  • You must press CTRL + SHIFT + ENTER to apply the formula if you are not using Excel365.
  • Remember to name the worksheet as the name used in the code or vice versa.
  • Don’t forget to save the file as a macro-enabled workbook.

Conclusion

Now you know the 4 effective ways to count the frequency of a text within a dataset in excel. Which method do you prefer the most? Do you have any further queries or suggestions? Please let us know in the comment section below.

Using worksheet functions in VBA is extremely time-saving and useful, by using the COUNTIF Worksheet function in VBA and the input box – in this case, we managed to get the input needed from the user as the criteria and then could count the frequency of the text.

Please feel free to comment and tell us which Worksheet functions you use in your VBA code.

You can also visit our ExcelDemy blog to learn more about excel. Stay with us and keep Learning.


Further Readings

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo