How to Count How Many Times a Word Appears in Excel
When it comes to counting, Excel has a variety of functions to offer starting from the COUNT function to COUNTA, to COUNTIF and COUNTIFS function.
Each of these functions has a unique counting feature of its own that aids in counting specific cells in Excel 🙈
But what if you want to count how many times a specific word appears in Excel? This can also be done, and to your surprise, there are multiple ways how you can do this in Excel.
This guide discusses all these ways with examples. So, make sure you’ve downloaded your free practice workbook for this guide here and read it till the end.
Count how many times a word appears in Excel using the COUNTIF function
If you want to count how many times a word appears in Excel, it is all about using the COUNTIF function.
Check this out. I have a set of words placed in Excel 🥫
Out of these words, I want to count the occurrences of the particular word “bean”.
Here’s how we do it.
Step 1) Write the COUNTIF function as follows:
Step 2) Specify the range from which the word is to be searched.
Step 3) Specify the criteria based on which cells are to be counted.
Since we are looking out for the word “bean”, we will write it as the criteria enclosed in double quotation marks.
Step 4) Press Enter.
There are 5 instances of the word “bean” in the given dataset 5️⃣
Easy and quick, right?
Count a word’s occurrence based on case-sensitive criteria
So, can we not count (in a case-sensitive manner) how many times a word appears in Excel?
Obviously, we can ✌
But not using the simple COUNTIF function. It will be a combination of the EXACT and SUMPRODUCT functions.
See here.
Step 1) Write the EXACT function as follows.
The EXACT function checks for a word’s exact appearance in a given cell range. It only returns TRUE if the word appears in the same case in the given range.
So, the EXACT function will only return TRUE for those cells where the word “BEAN” appears in capital letters.
This is an array formula and the result will be an array so, unless you’re subscribed to Excel 365 or 2021, press Ctrl key + Enter + Shift key together to execute it rightly.
Step 2) To turn the TRUE / FALSE to 1/0, add 0 to the above function.
Step 3) Nest the above formula into the SUMPRODUCT function as follows.
There you get the count of how many times the word “BEAN” (in capital letters) appears in the given dataset.
This is how you can run a case-sensitive count in Excel 🚀
Count a word’s occurrence based on multiple criteria
What if we want to check how many times a word appears in Excel but based on a criteria?
For example, below are the grades of some students in Studies, Attendance, and Behavior.
I want to see how many of them scored Good (so I want to count how many times the word “Good” appears in this data).
But not only Good, but I also want to see how many of them scored “Good” in all three arenas i.e., Studies, Attendance, and Behavior 🥇
Since this constitutes of multiple criteria, we’d use the COUNTIFS function to check this.
Step 1) Write the COUNTIFS function as follows;
Step 2) As the first criteria_range, supply the grades of students in Studies.
Step 3) As the first criterion, write “Good”.
Step 4) As the second criteria_range, supply the grades of students in Attendance.
Step 5) The second criterion will again be “Good”.
Step 6) Repeat the same for the grades of students in Behavior.
Step 7) Press enter to see the count of how many times the word “Good” appears for all three grading arenas simultaneously.
Appears twice which makes all the good sense.
Only Student A and Student E managed to score “Good” in all three grading areas 🎓
This is how you can count a word’s appearance in Excel based on multiple criteria.
Count a word’s occurrence using the Pivot Table
Another way to count how many times a word appears in Excel is by using the Pivot Table.
If there is the repetition of words within a row or column and you want it summarized in terms of count, the Pivot Table is your ultimate solution.
Here’s how you create one.
This data in Excel contains a list of items along with their prices 💲
I want to see how many times the word “bean” appears in this list.
Step 1) Go to the Insert tab > Tables Group > Pivot Table.
Step 2) In the Insert PivotTable dialog box, define this data as the table/range.
Step 3) You can choose to have it inserted in the existing or a new worksheet.
Step 4) Click okay, and the Pivot Table will be inserted.
Step 5) From the Pivot Table Fields pane on the right side, drag and drop “Items” to the Rows section.
Step 6) Drag and drop the field list “Prices” to the Values section.
Step 7) Click on the field added under Values.
Step 8) Select Value Field Settings.
Step 9) Choose “Count” from the type of calculation.
Step 10) Press okay.
And there you go!
Excel creates a PivotTable that outrightly shows the number of times the word “bean” appears in the list of items.
And not only for “bean” but for other items too. Pivot tables work as a shortcut to summarize any dataset to show insights 🏸
Using it smartly, you can count how many times a given word appears in Excel.
Conclusion
This tutorial brings together a handful of ways to count how many times a word appears in Microsoft Excel. From using formulas to Excel tools like Pivot Table, we have seen them all.
Enjoyed learning them? Make sure the learning doesn’t stop.
Hop on to the following Spreadsheeto blogs that discuss different ways to count characters and text in Excel deploying different methods.