Using Excel's COUNTIF Function to Count Specific Words

QI Macros Word Count macro will parse all of the text selected and give you a sorted list of those words.

word count macro output

But what if you want to count the specific words or text in an Excel spreadsheet? 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 or phrase.

=COUNTIF($A$2:$A$10,"=*"&C2&"*")

This formula will find every cell that has the word or phrase 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.

Counting Words with Countif Formula


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

Using Text-to-Columns to Split Text into Words

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

Counting Words with COUNTIF after Text-to-Column Split

Get the Idea?

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

Create these charts and diagrams in just seconds using QI Macros for Excel...
Try It Now!