How To Count How Many Times A Word Appears in Google Sheets

Last Updated on April 22, 2023 by Jake Sheridan

In this tutorial, you will learn how to count how many times a word appears in Google Sheets.

How To Count How Many Times A Word Appears in Google Sheets

Here’s our step-by-step guide on how to find the word count in Google Sheets.

Step 1

First, identify the range to use as the scope of the search. In our example, we want to find out how many times the word “thou” appears in the range A1:A14.

Step 2

We can find the number of times a string appears through the Find and replace dialog box. We can access this tool using the shortcut Ctrl + H.

Simply click on the Find button until you cycle through all the results.

Step 3

We can also count how many times a word appears in a text using the following formula:

=IF(A1=””,””,COUNTIF(SPLIT(A1,” “),”Sample Text”))

The formula creates an array of words through the SPLIT function. We then use the COUNTIF function to find the number of words in the array match our target string.

Step 4

We’ll use the AutoFill tool to find the count for every cell in our range.

Step 5

Afterward, we can simply add up all the results using the SUM function.

Step 6

Alternatively, we can use a single formula to achieve the same result. We just need to combine all text in our target range into a single string.

=IF(A1=””,””,COUNTIF(SPLIT(JOIN(” “,A1:A14),” “),”thou”))

We’ll use the JOIN function to convert our range of text values into a single string to search through.

Summary

This guide should be everything you need to learn how to count how many times a word appears in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.