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:
….where the “rng” is the range of cells you interested in and “txt” or “Substring” is the actual word you want to count.
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:
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.
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.