Home » QI Macros Tips » Counting Specific Words with COUNTIF

Not Sure How to Use Excel's COUNTIF Function?

Countif will count individual words.
QI Macros Word Count Wizard will count ALL of the words!

Go Deeper: Call Center 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/datamining.xlsx, we just need to add a formula.

The data is in rows A2:A10). 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