How to Count Number of Occurrences in Google Sheets ( Easy Guide )

In this tutorial, we will learn How to count number of Occurrences in Google Sheets

To count the number of occurrences in Google Sheets we can use the =UNIQUE() and =COUNTIF() functions

How to Count Number of Occurrences in Google Sheets: Step by Step Guide

Enclosed are the steps which you can follow to count the number of occurrences in Google Sheets.

Step 1: Open Google Sheets

Open your Google Sheets with data where you want to calculate the number of occurrences in a Column in Google Sheets

In this example, we have a Google Sheet with a list of Football teams where we will calculate the number of occurrences.

How to count number of Occurrences in Google Sheets

Step 2: Calculate the Unique value with the UNIQUE function

Now we will calculate the unique value using the UNIQUE function.

Excel Formula COUNTIFS With Multipl...
Excel Formula COUNTIFS With Multiple Criteria

SYNTAX

UNIQUE(range)
  • Navigate to the cell where you want to get the unique value in our example C2
  • Now type the formula in the cell  =UNIQUE(A2:A17) and press enter.
  • Here we have specified the range from cell A2 to A17, you can type the range as per your requirement.

Once we the UNIQUE function for the specified range it automatically calculates the unique teams for the specified range.

Step 3: Use the COUNTIF function to get the Unique occurrences

Now we will count the unique occurrence of Unique teams using the =COUNTIF() function

Syntax

COUNTIF(range, criterion)
  • Range: Here we need to specify a range for example A2-A17
  • Criterion: Here we need to specify the Cell number to apply a test or pattern in our example from Unique teams
  • Navigate to the cell where you need to calculate the count of unique occurrences.
  • Type the formula =COUNTIF($A$2:$A$17, C2), adjust the formula as per your data

Now press Enter,

Google Sheets auto-suggest will ask to automatically apply the formula on the remaining cell, Click on the checkmark

Now the formula is applied to the rest of the cells and we have the count for the rest of the Unique teams

Count Number of Occurrences with Partial Match

There might be a case where you need to count a cell with a Partial Match however the COUNTIF function can only count an exact match.

To solve this issue we will use an asterisk (*)  wildcard which is interpreted as a partial match by Google Sheets.

SYNTAX

=COUNTIF(range,”*”&criterion&”*”)
  • range – This is the range for which Google Sheets will perform the count
  • criterion – The pattern or test that should be applied to the range.

In the following example screenshot, we have used the below formula

=COUNTIF($A$2:$A$17,"*"&C2&"*")

  • Navigate to the cell where you need to calculate the count of unique occurrences with Partial Match.
  • Type the formula =COUNTIF($A$2:$A$17,”*”&C2&”*”), adjust the formula as per your data

Wrapping up

Using the preceding step-by-step guide you can easily get the unique occurrences and count with a few simple steps.

We hope this tutorial on How to Count Number of Occurrences in Google Sheets was helpful.

Related articles :

How To Transpose Data in Google Sheets

How to count non-empty cells in google sheets

How to Count Cells based on Color in Google Sheets

How to Get Absolute value in Google Sheets ( Easy Guide )