Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • How to create running total in an Excel Table
  • How to calculate correlation coefficient Correlation in Excel
  • How to add Trendline to a chart in Excel
  • How to do a t-Test in Excel?

References

  • Convert text string to valid reference in Excel using Indirect function
  • Basic INDEX MATCH approximate in Excel
  • Count unique text values with criteria
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Create hyperlink with VLOOKUP in Excel

Data Validations

  • Data validation must not exist in list
  • Excel Data validation exists in list
  • Excel Data validation date in specific year
  • Excel Data validation number multiple 100
  • Excel Data validation must begin with

How to count specific words in a range in Excel

by

To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions.

 Formula

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,text,"")))/LEN(text))

Explanation

In the example shown, the formula in C11 is:

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))

Note: The formula on this page counts instances of a word in a range. For example, if a cell contains two instances of a word, it will contribute 2 to the total count. If you just want to count cells that contain a specific word, see this simple formula based on the COUNTIF function.

Worked Example:   How to pad text to match equal length in Excel

How this formula works

In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count.

For each cell in the range, SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE.

Worked Example:   Count cells that contain odd numbers in Excel

Then, the number of characters removed is divided by the length of the substring. So, if a substring or word is 5 characters long, and there are 10 characters missing after it’s been removed from the original text, we know the substring/word appeared twice in the original text.

Because the above calculation is wrapped in the SUMPRODUCT function, the result is an array that contains a number for each cell in the range. These numbers represent the number of occurrences of the substring in each cell. For this example, the array looks like this: {1;1;0;1}

Worked Example:   Subtotal by invoice number in Excel

Finally, SUMPRODUCT sums together all items in the array to get the total occurrences of substring in the range of cells.

Ignoring case

SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a word or substring, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution:

=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE((UPPER(range)),UPPER(text),"")))/LEN(text))

Post navigation

Previous Post:

How to count specific characters in a cell in Excel

Next Post:

How to extract word containing specific text in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • How to use Excel XOR Function
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel

Date Time

  • Convert date to text in Excel
  • Extract time from a date and time in Excel
  • Get project end date in Excel
  • Convert decimal hours to Excel time
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel
  • Group times into unequal buckets in Excel

General

  • Lock Cells in a Worksheet Excel
  • Count cells that do not contain errors in Excel
  • How to calculate profit margin percentage in Excel
  • Freeze and Unfreeze Panes in Excel
  • Creating and Opening an existing file in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning