How to Count Whether Cells in a Range Contain a Word in Excel

by Danielle Cort

You could count the number of times a word appears in your table by hand, but that can be time-consuming and tedious, particularly if you have a large data set. Excel offers a function that can do the counting for you, provided you can specify exactly what you want it to count. Use the COUNTIF function if you have a single range to search.

1

Open the Excel file with a range you want to search.

2

Click an empty cell where you want the count to show up.

3

Enter:

=COUNTIF(

4

Highlight the range of cells you want to search by clicking on the first cell and dragging your mouse to the last cell in the range. When you release the mouse button, the range will have a dashed box around it. Alternatively, enter the cell numbers for the beginning and end of the range manually, separated by a colon. For example, you could enter "A5:D20."

5

Type a comma after the range. Do not include any spaces in the formula.

6

Type an open quote, followed by the word, followed by a closed quote, followed by a closed parenthesis. Press "Enter." For example:

=COUNTIF(A5:D20,"blue")

This search is not case-sensitive and will count "blue" as well as "Blue" and "BLUE." The formula will only count cells that have the exact word you specified and nothing else. For example, if you have cells with a phrase such as "arctic blue" or "blue whale," these cells will not be counted.

7

Include a wild card -- or asterisk -- on either side of your word if the word you are searching for is part of a string of words, and you want to include all cells with the word. For example, if you want to count every cell that has the word "blue" in it regardless if it is a single word in a cell or part of a string of words, such as "arctic blue," type "" followed by the word, followed by the "" again. For example:

=COUNTIF(A5:D20,"blue")

About the Author

Danielle Cort has been a freelance writer since 2008, specializing in psychology, health, education and parenting. She has published articles in "Family" magazine. Before becoming a freelance writer, Cort worked in the public policy research sector, conducting research, creating surveys and budgets. She holds a Doctor of Philosophy in social psychology from the University of Massachusetts.

Photo Credits

  • photo_camera Jupiterimages/Brand X Pictures/Getty Images