Home » QI Macros Tips » Counting Specific Words with COUNTIF

Using Excel's COUNTIF Function to Count Specific Words

QI Macros Word Count wizard will parse all of the text selected and give you a sorted list of those words. But what if you want to count the specific words used in text? A QI Macros customer was trying to determine the frequency of a glossary of words in text, but wasn't sure how to go about it.

Case Study - Using Countif

When I first started analyzing data collected by call center reps, I had to figure out how to use COUNTIF. It's not hard, but I needed to make it simple enough that I could change words in and out. If we take the Word Count data in QI Macros Test Data/Pivottable.xlsx, we just need to add a formula.

The data is in rows A2:A10 (see below). We need to use the fixed range ($A$2:$A$10) so that we can copy the formula. Then we need the criteria.

I could use the formula =COUNTIF($A$2:$A$10,"=*Delete*"), but I'd have to change the word "Delete" every time I wanted to add a new word. If we use a link to another cell ("=*"&C2&"*") that contains a word, we can make one formula work for any word.

This formula will find every cell that has the word in cell C2 embedded in the text. It looks for *Delete*. (The asterisks are wildcard characters that match any text before or after the word.) If there's more than one "Delete" in a cell, it will only count it once, so this isn't flawless, but it gives a good estimate.

What if we wanted a more accurate count? We could use Excel's Text-to-Columns feature to split the text at spaces:

Then we could use COUNTIF on the split data. Notice that we now show two "surgery" words instead of just one.

Get the Idea?

Excel can do some pretty amazing things, but sometimes you have to get creative to achieve the end result.

Other Charts Included in QI Macros for Excel