How to count how often a word or a value occurs in Excel?
In daily Excel tasks, it is common to encounter situations where you need to determine how many times a specific word or value appears within a single cell or across a range of cells. For example, you might want to check how frequently a certain keyword is used in textual data, count the appearances of a specific item in a dataset, or simply analyze content consistency. Accurately calculating occurrences can assist with data validation, text analysis, inventory checks, survey results, and more. This article introduces several practical methods for counting word or value occurrences in Excel and helps you choose the most suitable approach for your actual work requirements.
Count how often a word/value occurs in a single cell or range with formulas
Easily count how often a word/value occurs in a single cell or range with an amazing tool
Count occurrences using a VBA macro (custom macro method)
Count occurrences with COUNTIF or SUMPRODUCT formulas (case-insensitive/partial matches)
Count occurrences using Excel Pivot Table (summarize across ranges)
Count how often a word/value occurs in a single cell or range with formulas
You can apply the following formula to count how often a word or a specific value appears in a single cell or in a defined range. This approach is particularly suitable when you want a quick, direct result without needing additional tools or macros.
For counting how often a word appears in a single cell, select a blank cell where you want the result displayed, enter the formula below, and then press Enter:
=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,D1,"")))/LEN(D1))
If you want to count how often a word appears across a range of cells, modify the formula to reference the entire range. Enter it in a blank cell as follows:
=SUMPRODUCT((LEN(A2:A15)-LEN(SUBSTITUTE(A2:A15,D1,"")))/LEN(D1))
Notes:
Count how often a word/value occurs in a single cell or range with Kutools for Excel
The Count the number of a word feature in Kutools for Excel lets you instantly calculate how many times any word or value appears within a cell or a defined range. This method greatly simplifies counting for users who prefer not to work with formulas or VBA code, enhancing both efficiency and accuracy for repetitive tasks and large-scale data analysis scenarios.
1. Select a blank cell for the output. Then, navigate to Kutools > Formula Helper > Formula Helper.
2. In the Formula Helper dialog box, follow these configuration steps:
Tips: Use the Filter box to quickly find this formula if you know its name.
You will immediately see the total number of times your target word occurs within the chosen cell or range.
Limitation: Requires installation of Kutools for Excel.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Count occurrences using a VBA macro (custom macro method)
For users who require more flexible or advanced counting options—such as counting only whole word matches, switching between case-sensitive and case-insensitive counts, or automating the process over large datasets—a VBA macro-based solution is practical. This approach allows you to customize the matching mode, which can save time and improve consistency over repeated operations.
Applicable scenario: When you need tailored counting (e.g. ignore case, match whole words only), or want to automate counting as part of a workflow.
Precautions: Always back up your files before running macros, and enable macros only from trusted sources.
1. Open the VBA editor by clicking Developer Tools > Visual Basic. In the VBA window, click Insert > Module and paste the following code into the module:
Sub CountOccurrencesOfWord()
Dim rng As Range
Dim wordToCount As String
Dim caseSensitive As Integer
Dim wholeWordOnly As Integer
Dim totalCount As Long
Dim cell As Range
Dim cellText As String
Dim i As Integer
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to count:", "KutoolsforExcel", rng.Address, Type:=8)
wordToCount = Application.InputBox("Enter the word or value to count:", "KutoolsforExcel", "", Type:=2)
caseSensitive = MsgBox("Enable case sensitive count? (OK = Yes, Cancel = No)", vbOKCancel, "KutoolsforExcel")
wholeWordOnly = MsgBox("Count whole words only? (OK = Yes, Cancel = No)", vbOKCancel, "KutoolsforExcel")
totalCount = 0
For Each cell In rng
cellText = cell.Value
If Not IsError(cellText) And Len(cellText) > 0 Then
If wholeWordOnly = vbOK Then
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "\b" & wordToCount & "\b"
regEx.Global = True
regEx.IgnoreCase = (caseSensitive <> vbOK)
If regEx.Test(cellText) Then
totalCount = totalCount + regEx.Execute(cellText).Count
End If
Else
If caseSensitive = vbOK Then
i = (Len(cellText) - Len(Replace(cellText, wordToCount, ""))) / Len(wordToCount)
Else
i = (Len(LCase(cellText)) - Len(Replace(LCase(cellText), LCase(wordToCount), ""))) / Len(wordToCount)
End If
totalCount = totalCount + i
End If
End If
Next cell
MsgBox "Total occurrences: " & totalCount, vbInformation, "KutoolsforExcel"
End Sub2. After entering the code, click the 
Run button. A dialog will prompt you to select the range where you want to count, then let you specify your target word or value and choose whether to perform a case-sensitive count and/or count only whole word matches.
Tips:
- If you select "Count whole words only", partial matches (e.g., "he" inside "the") will not be counted.
- Use case-insensitive mode for more general surveys or analysis of mixed-case data.
- The macro ignores empty or error cells during the calculation.
Troubleshooting: If you encounter errors when running, check that your data does not contain formula errors or empty values. If your Excel installation blocks macros, enable macros in Trust Center Settings.
Summary suggestion: This VBA method is especially suitable when standard formulas can't meet your needs, and is easily extendable for more complex matching rules or repeated batch processing.
Count occurrences with COUNTIF or SUMPRODUCT formulas (case-insensitive/partial matches)
Excel provides several flexible formulas for counting both exact matches and partial matches. The following methods are applicable when you want a quick, automated solution that is easy to adjust. These approaches are ideal for lists of values, survey data, or filtering out specific occurrences within an entire table or column.
COUNTIF formula: count exact, case-insensitive matches
If you want to count the number of cells in a range that match a word or value (regardless of case), enter the formula below in a blank cell (e.g., E1):
=COUNTIF(A2:A15, D1)This counts the number of cells in A2:A15 that contain exactly the same value as D1. This is not case-sensitive ("Apple" and "apple" are counted together). After entering the formula, press Enter, and you can drag the fill handle to copy the formula to other rows if required.
COUNTIF with partial match (using wildcards)
To count the number of cells containing a specific word anywhere within the cell (not just exact matches), adjust the COUNTIF formula as follows and enter in a blank cell:
=COUNTIF(A2:A15, "*" & D1 & "*")This version counts cells where D1's content appears anywhere in the cell, still ignoring case. Useful for survey responses, product codes, or text fragments.
- A2:A15: The data range where you are searching.
- D1: The search term or reference cell.
Reminder: If you require case-sensitive, partial matches, consider using VBA as above.
Tip: All these formulas automatically adapt to new data if you expand your ranges.
Count occurrences using Excel Pivot Table (summarize across ranges)
Excel’s built-in Pivot Table feature is especially efficient for summarizing and counting how many times specific values or words appear in a list or table, especially when working with structured data such as lists, transaction records, or survey responses. Pivot Tables offer dynamic grouping and multi-criteria counting, making them a practical choice for data analysis without writing any formulas or code.
Value explanation: Ideal for summarizing large tables, identifying frequencies in survey answers, tallying up repeated names, or analyzing inventory item counts.
Advantages: No formulas needed, visual and interactive, simple updates with new data.
Disadvantages: Less suitable for counting within single cells or unstructured text content; best for column-wise value counting.
Steps to use Pivot Table for counting value occurrences:
- Select your data range (e.g., A1:A100 or the entire table).
- Go to the Insert tab and click PivotTable.
- Decide where to place the Pivot Table (new worksheet or existing worksheet) and click OK.
- Drag the relevant column (the one with the text/values to count) into both the Rows area and the Values area in the field list. The Pivot Table will automatically count how many times each unique value appears.
The results table can then be refreshed any time your original data changes, maintaining accurate counts with minimal effort.
Tips:
- You can filter values directly within the Pivot Table for more precise summaries.
- For detailed breakdown by multiple fields (such as counts by word and by category), simply add more fields to the Rows or Columns area.
Troubleshooting: If your Pivot Table does not update after changing the original data, right-click anywhere inside the Pivot Table and choose "Refresh". If you don't see the field list, click "Field List" on the PivotTable Analyze tab.
Related articles
Count Duplicate Values In A Column In Excel
This article demonstrates4 methods to help counting duplicate values in a column in Excel.
Count If Cell Contains Text Or Part Of Text In Excel
Supposing you have the below data, and want to count the number of cells which contain the text "Apple", number of cells contain the text "Orange" and cells contain "Peach" separately, how to get it done? This tutorial explains the COUNTIF function in detail to help you quickly count the number of cells if they contain text or part of specific text in Excel. Besides, it introduces an awesome feature to acheive it easily with only clicks.
Countif With Multiple Criteria In Excel
In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex, here, this article will talk about some items for counting with multiple criteria.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in