Google Sheets – Count cells containing specific text

If you’re using Google Sheets and you want to count the occurrences of a certain letter or word, there’s a pretty easy function that you can use.

Looking for a word and only that word

In this first set of data in the image above and also in this linked spreadsheet, we will be counting any cells that contain the word “Yellow” and only that word. To count the occurance of the word yellow in the range B2 to B9 you can use the count COUNTIF function as such: =COUNTIF(B2:B9,"yellow"). It performs a conditional count. In this case, only if the cell or ranges of cells is equal to Yellow. Yellow is not case-sensitive so this is going to pick up three instances even though B7 is not capitalized. If the COUNTIF technique is doing everything you need, then you’re done and there is no need to try anything more involved.

Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you’ll like that.

Looking for a word occurring anywhere a cell

COUNTIF with wildcard
COUNTIF with wildcard

In this second set of data, things will get a little bit more complicated. We are looking for a certain word that occurs anywhere in any of these cells. First, you want to use COUNTIF again and give it a range =COUNTIF(C2:C9, "*Yellow*"). For this example, the range will be C2 to C9. If it has the word yellow and anything before which is what asterisk means, and anything after it which is the second asterisk, then it should be counted. It just has to have yellow in some part of it. Anything can be nothing so it can start or end with yellow too. This function is also counting 3 because of the yellow plane, the yellow car, and the little yellow boat.

I hope that was helpful and now you know the formula for counting any occurrences of any word that you’re looking for.

 Live examples in Sheets

Go to this spreadsheet for examples counting specific text that you can study and use anywhere you would like.

0 thoughts on “Google Sheets – Count cells containing specific text

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>