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

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

Conclusion

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.

Yaniss Illoul

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

You might also like these posts:

Leave a Reply

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

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

function external_link_attribute() { ?>

more!

Tutorials, Case Studies, Content Curation, …

In your inbox, once a week.

more!

Tutorials, Case Studies, Content Curation, …

In your inbox, once a week.