Home » QI Macros Tips » Counting Specific Words with COUNTIF

Not Sure How to Use Excel's COUNTIF Function?

Here's a Quick Tutorial on COUNTIF in Excel.

COUNTIF will count individual words, BUT If you want to count ALL of the words in a range of text,

consider QI Macros Word Count Wizard!

Step by Step Instructions for Using COUNTIF in Excel

Define the function =COUNTIF
Define the range $A$2:$A$10.
Tip: Use a fixed range so that you can copy the formula.
Define the criteria "=*delete*"
Tip: Instead of a specific word, use a link to another cell. ("=*"&C2&"*") That way we can make one formula work for any word.

In the example below, the 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

COUNTIF is a Valuable Data Mining Tool, But QI Macros Word Count is Even Better.

Count Words and Phrases using QI Macros:

Select the cells with text.
Click on QI Macros menu > Data and Text Mining > Word Count Wizard.
QI Macros will parse your text into one- and two-word phrases, and sort the counts for you.