Counting Cells Containing a Word

Ben has a worksheet that contains text values in column A. He needs to calculate the number of cells that contain the word "priority." He knows how to do the count if "priority" is the only word in a cell, but that's not always the case. Most of the time the word is within a sentence or a sentence fragment contained in a cell. Ben doesn't need the number of times the word appears (it may appear twice in some cells), but only the number of cells that contain the word at least once.

There are several ways you can accomplish this task. There are some questions that should be answered, though, before choosing a formula. For instance, do you want the count to be case sensitive? Do you only want to count "priority" when it is a whole word, or do you want to count when it is part of another word ("apriority")? What version of Excel is being used?

An easy way to count instances and take most of these questions into account is to use Find and Replace. Select the range of cells for which you want a count and use the Find tool (Ctrl+F) to search for "priority." Make sure, in the Find and Replace dialog box, you set the Look In drop-down list to Values and check the Match Case check box as desired. When you click on Find All, the dialog box reports the number of occurrences in the selected range—the number of cells in which "priority" appears.

If you want to use the count in a formula, then you need to use a formula to derive the count. The easiest approach is to use the COUNTIF function with a search string that includes wildcards:

=COUNTIF(A:A,"*priority*")

This counts the number of cells in column A that contain the word "priority" either by itself or surrounded by any number of characters. The matching is not case sensitive, so it will match "priority" or "Priority" or "PRIORITY" just the same. Here's a variation that will return the same result:

=COUNT(SEARCH("priority",A:A))

If you want the count to be case sensitive, then you can use a formula that is a bit more complex:

=SUM(N(A1:A9<>SUBSTITUTE(A1:A9,"Priority",)))

Simply put, within the quote marks, the mix of upper- and lowercase you want to use.

If you want to only count "priority" when it is a whole word, then you are going to need to use a formula that relies on the REGEXTEST function, which is available in Excel XXX and Excel in Microsoft 365. This variation is case sensitive:

=SUM(--REGEXTEST(A:A,"\bpriority\b"))

By default, REGEXTEST pays attention to case. You can make the formula case insensitive by adding an additional parameter to the end of the function:

=SUM(--REGEXTEST(A:A,"\bpriority\b",1))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11109) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.