You can use the following formula to count the occurrence of a specific word in Google Sheets:
=COUNTIF(B2:B15, "*Guard*")
This particular formula will count the number of cells in the range B2:B15 that contain the word “Guard” somewhere in the cell.
The following example shows how to use this formula in practice.
Example: Count Specific Words in Google Sheets
Suppose we have the following dataset that contains information about various basketball players:
We can use the following formula to count the number of cells in column B that contain “Guard” somewhere in the cell:
=COUNTIF(B2:B15, "*Guard*")
The following screenshot shows how to use this formula in practice:
From the output we can see that 6 cells in column B contain “Guard” somewhere in the cell.
We can confirm this is correct by manually identifying each of these cells:
To find the count of cells that contain a different word, simply replace Guard with another word in the formula.
For example, use the following formula to count the number of cells that contain “Forward” somewhere in the cell:
=COUNTIF(B2:B15, "*Forward*")
Note: You can find the complete documentation for the COUNTIF function here.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Extract Substring in Google Sheets
How to Reverse a Text String in Google Sheets
How to Extract Numbers from String in Google Sheets