How to Count the Number of Times a Word Appears in Excel

You can easily count the number of times a value appears in Excel using formulas. But there are other options that you can use such as Addins and VBA codes. By using an add-in, you get even to highlight the duplicate values. 

In this article, I will guide you step-by-step step on how to count the occurrence of a value in Excel

Use Unique and CountIF to count the number of times a word appears in a column

Unique function will help us to identify all the unique values while COUNTIF function will help us count the occurrence of each unique value. Note that the unique function is only available to the newest Excel versions such as Excel 365, Excel for The Web, Excel 2021, Excel for Android, and Excel for iPhone. Here are the steps: 

1. Enter the data

First type the data on a spreadsheet with the data you want to count the  values occurrence

2. Use unique() function to find the unique values in the column

In this step, you need to use =unique() to create a separate list of all the unique items

To do this just type the function then use the mouse to select the range. Alternatively, you can type the function and then type the range manually.

The formula will look something like this =UNIQUE(A2:A20)

using unique function to find unique values

If you are using an older version of Excel, you can use the copy unique values method

First, highlight the name including the header then click on data tab > advanced> copy to another location then select the location. Next tick on the option labeled unique records only.  then click okay

How to copy only unique records

3. Count the number of times  each word appears

Now you can count the number of occurrences of each word using the COUNTIF function. To do this type the following formula to the adjacent cell with the first unique name and press enter.


Now apply the formula to the rest of the cells by double-clicking the fill handle or dragging it down

How to count only unique values  using the countif Function

As you can see from the image above, we can see the frequency of each unique team

This method can be useful especially when you have a long list of words or values and you want to see the frequency. For example, you can count  the number of occurrences of employees, cars, students, products and so much more

Use Sum and IF

You can also combine sum and functions to achieve the same results. For example, to count how many times Bayern appears in the column,I will use the formula.


How to use SUM and IF to count number of occurrence in Excel

In this formula A2:A20 refers to the range that contains the list of words. “Bayern” is the word I want to count the number of times it appears. 

Use SumProduct to count how many times a word appears in a cell

Consider the paragraph below.

“Once upon a time, there was a man named Jake. Jake had another man friend named Trevor”

If I paste it into Excel then I can use the formula  =(LEN(A2)-LEN(SUBSTITUTE(A2, “man”,””)))/LEN(“man”) to calculate how many times the word Man appears in that cell. 

You can replace the word “man” with the word you want to count the number of times it appears and cell “A2” with your actual cell.

Using VBA code

VBA code helps to extend Excel functionalities or make the existing ones even easier. This is even the perfect solution if you are working with a large set of data. Here are the steps

1. Open the VBA code editor using the shortcut alt+f11 on Windows or fn+f11 on Mac. Alternatively, you can add the developer tab and access the editor through this method

2. Click on Insert then select Module.

3. Copy and paste the following VBA code into the editor

Sub CountOccurrencesInText()
    Dim targetWord As String
    Dim searchRange As Range
    Dim caseSensitive As Boolean
    Dim cell As Range
    Dim count As Long
    ' Get user input for the target word
    targetWord = InputBox("Enter the word to count:", "Word Count")
    If targetWord = "" Then Exit Sub ' User canceled or entered empty string
    ' Get user input for the search range
    On Error Resume Next
    Set searchRange = Application.InputBox("Select a range to search:", Type:=8)
    On Error GoTo 0
    If searchRange Is Nothing Then Exit Sub ' User canceled
    ' Ask user whether the search should be case-sensitive
    caseSensitive = MsgBox("Do you want the search to be case-sensitive?", vbYesNo + vbQuestion) = vbYes
    ' Loop through each cell in the selected range
    For Each cell In searchRange
        Dim words As Variant
        Dim word As Variant
        ' Split the cell value into words
        words = Split(cell.Value, " ")
        ' Loop through each word
        For Each word In words
            If Not caseSensitive Then
                ' Convert both word and targetWord to lowercase for case-insensitive comparison
                If LCase(word) = LCase(targetWord) Then
                    count = count + 1
                End If
                ' Case-sensitive comparison
                If word = targetWord Then
                    count = count + 1
                End If
            End If
        Next word
    Next cell
    ' Display the result
    MsgBox "Occurrences of '" & targetWord & "': " & count, vbInformation
End Sub

4. Click on the green run button to run the code

How to run the VBA code

5. A prompt will ask you to enter the word you wish to count a number of times it appears. Type it and press ok.

type word to count

6. A new dialogue box will ask you to type the range or select using a mouse.

enter the range to count

7. Lastly, a dialogue box will ask you if you want the search to be case-sensitive or not. 

choose whether its case sensitive or not

A final window will pop up showing the occurrence of the word that you want to type

How to count the number of times a word  appears in a range of paragraphs

If you are dealing with cells that contain paragraphs or longer sentences then you need to use a different formula. 

For example, I will use =SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4, C2,””)))/LEN(C2)) to count the number of times the word “man appears in the cells range A:2:A4. Note that C2 is the cell holding the word that I want to search.

how to count occurrence of a word in a cell

As you can see from the image above the word man appears 5 times. 

However, SUMPRODUCT function is designed to be a case-sensitive function. So if we had the word “Man” it would have ignored it since we specified “man” in cell C2.

Look at the image below and see how the formula  counts 4 times after changing the case for one word

To make this formula case insensitive you can incorporate the upper or lower function. For instance, =SUMPRODUCT((LEN(A3:A4)-LEN(SUBSTITUTE((UPPER(A3:A4)),UPPER(C2),””)))/LEN(C2))

Use Addin

Lastly, you can use a plugin to count occurrences based on various criteria. Use Ablebits or the Kutools adin from Extend Office. Alternatively, you can search in the Office addin store directly from your Excel document. 

Download Practice worksheets

Final Thoughts

That’s how to count the number of times a word appears in a cell, a column, or a range in Excel. You can either use an addin, VBA, or Excel functions. All these methods will give you results depending on your scenario. One method may be better than the other one based on your data. 

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading