How to Count Whether Cells in a Range Contain a Word in Excelby 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.
Open the Excel file with a range you want to search.
Click an empty cell where you want the count to show up.
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."
Type a comma after the range. Do not include any spaces in the formula.
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.
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*")
- photo_camera Jupiterimages/Brand X Pictures/Getty Images