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)
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
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.
=COUNTIF(A2:A20,B2)
Now apply the formula to the rest of the cells by double-clicking the fill handle or dragging it down
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.
=SUM(IF(A2:A20=”Bayern”,1,0))
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
Else
' 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
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.
6. A new dialogue box will ask you to type the range or select using a mouse.
7. Lastly, a dialogue box will ask you if you want the search to be 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.
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.
