Table of Contents
Counting specific words in Excel can be done using the COUNTIF function. This function takes a range of cells and a single criterion to count the number of cells containing that criterion. For example, if you wanted to count how many cells in a range contain the word “Apple”, you could use the COUNTIF function with the range of cells and the criterion “Apple”. This would provide a count of all of the cells that contain the word “Apple”. The same process can be used to count other words, phrases, and numbers.
You can use the following formulas to count the occurrence of specific words in Excel:
Method 1: Count Occurrence of Specific Word in Cell
=(LEN(A2)-LEN(SUBSTITUTE(A2,"word","")))/LEN("word")
This particular formula counts how many times “word” occurs in cell A2.
Method 2: Count Occurrence of Specific Word in Range
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"word","")))/LEN("word"))
This particular formula counts how many times “word” occurs in the cell range A2:A8.
The following examples show how to use each formula in practice with the following column of text in Excel:
Example 1: Count Occurrence of Specific Word in Cell
We can type the following formula into cell B2 to count how many times the word “Three” occurs in cell A2:
=(LEN(A2)-LEN(SUBSTITUTE(A2,"Three","")))/LEN("Three")
We can then drag and fill this formula down to each remaining cell in column B:
Column B shows how many times the word “Three” appeared in the corresponding cell in column A.
Note: This formula is case-sensitive. For example, the word “three” will not be counted.
Example 2: Count Occurrence of Specific Word in Range
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"Three","")))/LEN("Three"))
The following screenshot shows how to use this formula in practice:
We can see that the word “Three” occurs a total of 6 times in the cell range A2:A8.
To create a case-insensitive formula, we can use the UPPER function in Excel as follows:
=SUMPRODUCT((LEN(A2:A8)-LEN(SUBSTITUTE(UPPER(A2:A8),UPPER("Three"),"")))/LEN("Three"))
We can type this formula into cell B10 to count how many times “Three” (regardless of case) occurs in the cell range A2:A8.
We can see that the word “Three” (regardless of case) occurs a total of 8 times in the cell range A2:A8.