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.

Table of Contents

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 🥫

Words in Excel sheet

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:

Click to copy
use COUNTIF function

Step 2) Specify the range from which the word is to be searched.

Click to copy
cell reference for range of cells of search

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.

Click to copy
Criteria in Excel spreadsheet

Step 4) Press Enter.

COUNTIF formula counts number of cells with bean

There are 5 instances of the word “bean” in the given dataset 5️⃣

Easy and quick, right?

Pro Tip!

Note that the word bean appears in the specified range in different cases, sometimes capital case sentence case, or even small case.

However, we have specified the word “bean” in small letters in the criteria. But this doesn’t make a difference to the results. The COUNTIF function has counted all instances of the word “bean” irrespective of its case.

This proves that the COUNTIF function is not case-sensitive.

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.

Click to copy

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.

Kasper Langmann, co-founder of Spreadsheeto
EXACT function checks for case

Step 2) To turn the TRUE / FALSE to 1/0, add 0 to the above function.

Click to copy
Adding 0 to the EXACT function

Step 3) Nest the above formula into the SUMPRODUCT function as follows.

Click to copy
SUMPRODUCT excel functions

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.

Students grades in Excel

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;

Click to copy

Step 2) As the first criteria_range, supply the grades of students in Studies.

Step 3) As the first criterion, write “Good”.

Click to copy
COUNTIFS syntax

Step 4) As the second criteria_range, supply the grades of students in Attendance.

Step 5) The second criterion will again be “Good”.

Click to copy
second criteria for worksheet function

Step 6) Repeat the same for the grades of students in Behavior.

Click to copy
Excel formula for COUNTIFS

Step 7) Press enter to see the count of how many times the word “Good” appears for all three grading arenas simultaneously.

Count words and text string

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 💲

List of items and 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.

Insert PivotTable

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.

PivotTable dialog box

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.

PivotTable fields

Step 7) Click on the field added under Values.

Step 8) Select Value Field Settings.

Value field settings to count number

Step 9) Choose “Count” from the type of calculation.

Step 10) Press okay.

Type of calculation set to Count occurrences

And there you go!

Number of occurrences of bean in column A

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.