How to Count Specific Words in Google Sheets

If your boss asked you to find out how many times a certain word appears in your spreadsheet, you might get a slight panic attack. Don’t worry, as you don’t have to spend hours counting! We’ve got you covered.

There’s an option in Google Sheets that can calculate specific words or phrases for you. In this article, we’ll show you how to use that option. Plus, we’ll explain some similar options to avoid confusion.

The Count If Option

As you may have guessed, we’re talking about the option called Count If. This option identifies and counts cells that have a particular value that you’ve specified. It can be anything: words, numbers, phrases, or even formulas. It’s handy for people who have to deal with a lot of data daily.

All you have to do can be summarized in two steps:

You have to specify a range of cells you want to check or select the whole spreadsheet.
You have to specify which word or number you want Google Sheets to count for you.

That’s it! However, if you’ve never used this option before, you can find more details in the guide below.

Step-by-Step Guide

To make it more practical, let’s say we want to know how many times the word “customer” appears in column A. If you’re ready, open your spreadsheet, and we can get started.

Click on an empty cell.
Enter the following formula: =COUNT IF (A1:A10, “customer”).
The result will appear in that cell.

There you have it! So, the first part of the formula is the range of cells you want to check. In our example, we were interested only in cells from 1-10. The second part of the formula contains the word you want to count. Don’t forget to write it with quotation marks; otherwise, you won’t get the right result.

Users who want to check the occurrence of multiple words can dedicate a whole column to the results. In that case, you can type something like “Results” in the first cell, and then go on and count each word you’re interested in. The best thing is that you can export these results from Google Sheets or send them to your colleagues.

Counting the Cells That Don’t Contain Specific Words

On the other hand, if most of the cells contain a specific word, you may want to know how many cells don’t include that word. You could use the formula mentioned above and then calculate it yourself. But there’s no reason to lose time on that, as you can do it automatically.

We’ll now show you a modified Count If formula. Let’s say you want to know how many cells don’t contain the word “customer”. Here’s what you have to do:

Click on an empty cell.
Enter the following formula: =COUNT IF (A1:A10, “<> customer”).
The result will appear in that cell.

As you can see, the Count If function is very sensitive. Two small punctuation marks can change everything. Therefore, you have to be very careful when using Google Sheets to avoid getting the wrong result.

Partial Match

There’s another option that looks similar to the first option we described. However, it probably won’t give you the desired results. Why? Well, some articles suggest you should type the following formula in order to count all cells that contain the specific text: =COUNT IF (A1:A10, “*customer*”).

And that’s fine. But bear in mind that we used the word “text” instead of “word”. What’s the difference? This formula will identify all cells that contain the text you entered, even if it’s not in the form of a separated word.

To illustrate this, here’s an example. A business owner may want to count how many times the name “Mark” appears in a specific spreadsheet. If he uses the partial match formula, the formula will not only count the cells that contain the customer’s name, but it’ll also count cells with words like “marketing”. Because “Mark” is included in “marketing”, right?

Moreover, the Count If function is usually not case-sensitive, which can lead to even more confusion. Therefore, you have to be very careful with specifying what you’re looking for. Google Sheets has a solution for almost everything. However, make sure you’re using reliable sources to get informed.

There’s So Much More

Most people don’t use Google Spreadsheets to its fullest potential. Instead, they use it as a simple spreadsheet tool, and we believe that’s a shame as this app has a lot to offer. Obviously, we’re not talking only about the Count If option, although it’s very useful. You may be surprised to discover that there are some even better options.

Therefore, don’t stop here, go ahead and explore all the options of this app.

What do you usually use Google Sheets for, and what are your favorite features? Let us know in the comments section below.