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.
![count if formula example](/free-excel-tips/counting-words-with-countif/countif-formula-excel.png)
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.
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.
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.
Stop Struggling with Word Counts!
Start creating your Word Counts in just minutes.
Download a free 30-day trial. Get Word Counts now!
Other Charts Included in QI Macros Add-in for Excel