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.
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:
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.
- Just enter the preferred text cell F5 and then apply the following formula in cell G5.
2. Count Frequency of Text Using SUM and IF Functions
- Apply the following formula in cell G5 instead to do that.
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.
- 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.
- Now drag the Preferred Grip field to the Rows and Values area in the PivotTable Selection Pane.
- After that, you will get the desired result as follows.
4. Count Frequency of Text Using Excel VBA
Follow the steps below to apply VBA to count the frequency of specific grips.
- 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.
- 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.
- Next, select View >> Properties Window in order to see the 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”.
- 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”.
- 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.
- After that, right-click the button and select View Code as shown.
- 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
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).
- Then, right-click the newly added button, choose View Code, and enter the following code for the button click event:
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.
- After that, change the name of the button to cmdForm and the caption to “Check the Frequency of Text Form”, while in Design mode.
- Then right-click the newly created button on the worksheet, choose View Code, and, enter the following code for the button click event:
- Next, return to the worksheet, make sure Design mode is unchecked, and, click on the button to show the form.
- 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.
- Then type Continental Grip in order to see the number of times Continental Grip appears in the range and then click Ok.
- After that, the textbox appears stating the number of times Continental Grip is mentioned in the range.
- 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.
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.
- COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)
- How Do I Count Cells with Text in Excel
- Count If a Cell Contains Any Text in Excel (4 Methods)
- Excel VBA to Count Cells Containing Specific Text
- How to Count Filtered Cells with Text in Excel (3 Methods)
- How to Count Cells in Excel with Different Text (5 Ways)