How to Count Number of Occurrences in Google Sheets


You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Google Sheets.

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, let’s enter the names for some professional basketball teams in column A:

Step 2: Find the Unique Values in the Column

Next, let’s use the =UNIQUE() function to create a list of every unique team name in column A:

Note that this function creates an array of unique values by default.

Step 3: Count the Occurrence of Each Unique Value

Next, let’s use the =COUNTIF() function to count the number of occurrences of each unique team name:

Note that we simply copy and pasted the formula in cell E2 to each of the remaining cells in column E.

From the output we can see:

  • The team name ‘Hornets’ occurs 1 time in column A.
  • The team name ‘Hawks’ occurs 3 times in column A.
  • The team name ‘Spurs’ occurs 5 times in column A.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Find Outliers in Google Sheets
How to Sum Values by Category in Google Sheets
How to Perform a Median IF Function in Google Sheets

Featured Posts

6 Replies to “How to Count Number of Occurrences in Google Sheets”

  1. Hello there,

    I would like to ask a question here as I am stacking badly with the task I have been assigned to …
    I am very new using Google Spreadsheets and very well stacking wit the task i have been assigned to ..

    I have Google spreadsheet with the data that includes Countries, number of artists from those countries and also entries.
    Now I have have to get numbers of countries, people and entries from that Google spreadsheet.

    Would you be able to help me out how to get those numbers …?

    I am not sure if it is clear enough as I am not native speaker.

    Thank you very much.
    Robert

  2. How do I apply this formula across three columns? I have a form with three questions that each contains the same list of names. Respondents select one name from each list. I want a formula that will pull together a list of unique names across the three columns then tell me the number of occurrences for each name across those same columns. Basically, it’s a ballot. I want to know which name had the most votes, second-most votes, third-most votes, etc.

  3. Question: I have a Department, Monitor Model & Monitor Model 2 columns
    How would I find the number of monitors for each department?

    Thx for any help ..

  4. Hi, how would I count all mentions of a word in a sheet, even if the word is used twice in one cell? Example

    A1=”dog”
    A2=”cat”
    A3=”dog, dog”

    I want to count all 3 mentions of “dog”

  5. Zach
    thanks for the info!

    I’m trying to make a dot plot in sheets.
    It requires having a column for the current frequency. Is there a function for this? ie

    input: (2 3 3 4 4 4 5 6 6 6 6 7 9 9 )
    output:(1 1 2 1 2 3 1 1 2 3 4 1 1 2)

    regards

Leave a Reply

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