SUMPRODUCT To Count Occurrences Of Specific Words In Cell Range

Hello, Excellers and Happy Friday!. Yes, Happy #formulafriday. It is time for some more Excel formula fun. Today let’s look at how to use the fantastic SUMPRODUCT function to count the occurrences of specific words in a range of cells.

I have found this a REALLY useful way to analyze keyword data, or indeed feedback or comments. In these situations you can have a lot of text records and analyzing text may not seem easy at first. I have even used it for SEO analysis, look

SUMPRODUCT Function Refresher

If you are new to the SUMPRODUCT Function (it’s really cool…just wait!), or in need of a quick refresher then we can take a couple of steps backward and cover the basic of this awesome formula.

Let’s take a look at what exactly it does. In basic terms, it multiplies ranges or arrays together and sums the results. So first it multiplies, then it sums. It is a bit like using COUNTIF or SUMIF but with extra power. We can take a walk through the syntax first to see how this function can help solve this business problem we have to count occurrences of out specific words.

Here is a sample of the data set we are working with. We have our keyword data in Column A, and the SUMPRODUCT Function used in D2 to count the occurrences of the keyword in cell C2.

This is the formula that we are using

=SUMPCRODUCT(–(ISNUMBER(FIND(C2,A2:A10)))

How Does The Formula Work?

Let’s take the formula one step at a time. The FIND part of the formula searches for the word in each of the cells in the range. It returns the starting position of the word. It returns an error if the word in cell C2 is not found in the range of cells.

The ISNUMBER part of the formula then returns true if a number is returned, or false if not from the FIND function. The double hyphens in front of the formula will simply convert the TRUE or FALSE to 1 or 0.

Finally SUMPRODUCT then sums the 1’s and 0’s. How clever is THAT?.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and you will automatically receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series or the Macro Monday Series Click The Link Below