Join theĀ top marketers who read our newsletter each week:

How to find the Frequency of a list of words in a column in Google Sheets

Table of Contents

Finding how many times a word or a string appears in a series in Google Sheets is not a hard feat. In this article, I want to show you quickly how to achieve this research.

1st Step: Finding Unique Values

The first step in achieving this project is to figure out which are the unique values of the dataset we are exploring.

In my example, I have the list of every team that won the Formula One Championship from 1950 to 2018. My goal is to find which team won the most championships. Because some teams obviously won the trophy multiple times, I need to isolate first a list of team that contains only one of each of the team names.

To do that, we are using the formula =UNIQUE. This is how it works:

And this is how we are using it with my dataset:

From the 70 different rows I had in column A, the Unique formula found the 16 unique values in my dataset.

Using this formula can also highlight discrepancies in how the data has been collected. For instance, if one of the strings in column A was “Ferrari ” with a space afterwards, the Unique formula would have capture two instances of the word Ferrari. Always think about rapidly checking your column fo unique values in case the data could’ve been collected wrongly.

Final Step: Counting the Occurrences

Google Sheets has the perfect formula to count the number of occurrences of a string in a dataset or column. This formula is called =COUNTIF and this is how it works:

And this is how we would use it in my spreadsheet:

And this is how it looks when we drag the formula to face every unique value:

And this is it. We are done here. For entertainment purpose, I allowed myself to share a few of the charts I made to illustrate the dataset I got my hands on regarding this whole Formula one topic.

# of F1 Championships won from 1950 to 2018 per team

This chart is the one I was looking for before starting this tutorial.

Evolution of # of Championships won by Team from 1950 to 2018


This article is way shorter than usual but it did not warrant more really… If you have any questions regarding this article, please let me know in the comments section down below or send me an email.

Join the top marketers who read our newsletter each week.

Yaniss Illoul

Share on twitter
Share on linkedin
Share on facebook
Share on reddit

You might also like these posts:

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Get more tutorials, guides and curated content !

In your inbox, once a week.


Get more tutorials, guides and curated content !

In your inbox, once a week.