Using Excel's COUNTIF Function to Count Specific 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.

count if formula example

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.

count if formula example


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

using text to columns to split text

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.

Stop Struggling with Word Counts
Start using QI Macros Word Counts in just minutes.

Download a free 30-day trial. Get QI Macros Word Counts now!

Other Charts Included in QI Macros for Excel