Count specific words in a range

Counting specific words in a range of cells is simplified by the use of Excel. If you’ve Excel data that you wish to test how many times a particular word appears in given range, use a formula based on LEN, SUBSTITUTION and SUMPRODUCT functions. For counting words in a range, the formula is an advancement of counting words in a cell. Here’s how the formula looks:

Generic Formula

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

….where the “rng” is the range of cells you interested in and “txt” or “Substring” is the actual word you want to count.

Application Example

For this example, we are going to look at a range of cells and count how many times a particular word or substring appears inside the range. Check out how we have solved the problem in this example.

Figure 1. Example 1 of LEN/SUBSTITUTION/SUMPRODUCT Functions

From the screenshot, the formula in the highlighted cell (C12) is:

=SUMPRODUCT((LEN(B6:B10)-LEN(SUBSTITUTE(B6:B10, E6,"")))/LEN(E6))

Here’s How the Formula Works

We are counting the times the word “you” is appearing in the range B6:B10. SUBSTITUTE eliminates the word “you” from the original text in B6:B10. LEN then count the length of the text in the range without “You” and the result is subtracted from the original text’s length. The result from subtraction are characters removed by the SUBSTITUTE.

The number of missing characters is then divided by substring length. For instance, if you have 8 missing and your substring gas 4 characters, then it is an indication that it has appeared twice. The same is done for all cells and finally, SUMPRODUCT sums the counts from all cells. In this example, the word “you” is appearing 6 times.

Case Sensitivity

The function used for C12 is case sensitive. That is the reason why C10 result is 1, yet there are 2 “you” in the test. However, the first “you” have an uppercase. You can have an UPPER function for the text to be converted to upper case before substituting. Here’s the formula that has been used for C12.

Figure 2. Example 2 of LEN/SUBSTITUTE/UPPER Functions

We have also used a case-sensitive formula for C10.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar