How to count unique values in Google Sheets

By SpreadCheaters
Page last updated: 21/06/2023 | Next review date: 21/06/2025
close

Author bio


SpreadCheaters
View More north_east

Counting unique values in Google Sheets means determining the number of distinct or different values that appear in a given range of cells in a sheet. This count should only include each unique value once, even if it appears multiple times in the range. Counting unique values in Google Sheets is important because it enables users to better understand and analyze their data.

Our dataset includes information about an apartment’s residents, including their names and occupations. We want to identify the unique occupations in the dataset, as some residents may share the same occupation. We have two methods for doing this: the first method is to determine the total number of unique occupations, while the second method involves identifying the frequency of each occupation.

Method 1: Count the total number of Unique values

Step 1 – Select the cell

  • Click on the Cell where you want to show the number of unique values

Step 2 – Use the COUNTUNIQUE function

  • After selecting the cell, use the COUNTUNIQUE function by typing:
  • =COUNTUNIQUE(

Step 3 – Type the Argument of the function

  • After using the function, type its arguments 
  • Here we have used B2:B11 as the argument
  • After typing the argument, type the closing bracket “)”

Step 4 – Press the Enter key

  • After typing the argument, press the Enter key to get the required result

Method 2: Count the Frequency of the Unique values

Step 1 –  Select the cell

  • Click on the Cell where you want to show the number of unique values

Step 2 – Type the Formula

  • After selecting the cell, type the following formula:

={UNIQUE(B2:B11),ARRAYFORMULA(COUNTIF(B2:B11,UNIQUE(B2:B11)))}

Step 3 – Press the Enter key

  • After typing the argument, press the Enter key to get the required result

Explanation of the formula used:

={UNIQUE(B2:B11),ARRAYFORMULA(COUNTIF(B2:B11,UNIQUE(B2:B11)))}

The first segment of the formula i.e. UNIQUE(B2:B11) returns an array of unique values in the range B2:B11.

UNIQUE(B2:B11) is used as an argument for COUNTIF(B2:B11,UNIQUE(B2:B11)), which returns an array of counts for each unique value in the range B2:B11.

The combination of these two functions (COUNTIF and UNIQUE) within an ARRAYFORMULA creates a table of unique values and their corresponding counts.