Do you want to count the number of unique items in your Google Sheets data?

Counting unique values in a dataset is a very useful way to analyze data because it provides valuable insights into a dataset.

When you count unique values in a dataset, you get to identify duplicate or irrelevant data points which may need removing or cleaning before further analysis.

In addition, counting unique values is usually essential for understanding and interpreting a dataset. It’s an essential part of any data analysis.

In this post, you will learn the different ways to count distinct values in Google Sheets.

What’s the Difference Between Unique and Distinct?

It’s important to discuss the difference between unique and distinct values as these terms are often mixed up. Understanding the difference between the two is essential because they can result in different counts.

Unique values refer to all values that appear just once in the selected range. Distinct values are all the different values present within a selected range.

For example, suppose you have a set of values given as {1, 3, 5, 1, 3, 2, 6, 1}. The unique and distinct values from this set are as follows.

• Unique values are {2, 5, 6} and the count will be 3.
• Distinct values are {1, 2, 3, 4, 5, 6} and the count will be 6.

📝 Note: Check out this post if you want to count the number of distinct values in your data.

Count Unique Values with the COUNTA and UNIQUE Functions

The UNIQUE function is one of the array formulas in Google Sheets. It extracts and returns all the values from a range of cells that appear just once. That said, the UNIQUE function can also be used to return and remove duplicates.

= UNIQUE ( range, [by_column], [exactly_once] )

The UNIQUE function uses three arguments.

• range – this points to the cells containing the values that will be filtered for unique entries.
• [by_column] – this is an optional argument that takes either a true or false. This argument is set to false by default and will filter the range by row. The by_column argument is useful when you select a range with multiple columns, and you want to filter by column as opposed to the default row filter. In this case, set the argument to true.
• [exactly_once] – This is another optional argument that takes a true or false. With this argument, you can make the function return values that appear just once or values that appear more than once. The argument is set to false by default.

=UNIQUE(C3:C18, false, true)

This syntax will return all the items that appear only once in the Make column.

To return a count of these values, we’d introduce the COUNT function.

COUNTA Function

The COUNTA function returns the number of non-empty cells in a range.

= COUNTA ( value1, [value2, …] )

It has a single argument value that can take one or multiple ranges and return the number of cells that are not empty.

To return the number of unique values in the range, wrap the UNIQUE syntax in the COUNTA function.

= COUNTA ( UNIQUE ( C3:C18, false, true ) )

This above formula syntax will return a count of all the values that appear exactly once in the Make column.

Count Unique Values with the ARRAYFORMULA and COUNTIF Functions

In Google Sheets, there are some functions that you need only apply in one cell, and they fill up an entire range. These are known as array functions.

The ARRAYFORMULA function gives non-array formula functions the ability to apply themselves to an entire range of cells, rather than just a single cell.

= ARRAYFORMULA ( array_formula )

The ARRAYFORMULA function allows you to use the same formula on multiple cells without having to use the drag-down functionality to copy the formula to other cells.

ARRAYFORMULA takes a formula as its only argument.

COUNTIF

The COUNTIF function returns the number of cells within a range that meets specific criteria.

= COUNTIF ( range, criterion )

COUNTIF has two arguments.

• range – refers to the cells containing the values you want to count.
• criterion – the condition for counting the values in the range.

Here’s how to use the ARRAYFORMULA and COUNTIF functions to count unique values.

= ARRAYFORMULA ( COUNTIF ( B6:B21, B6:B21 ) )

Create a new column – Number of occurrences – and use the syntax to generate the number of times each item in the Make column appears in the range.

= COUNTIF ( C6:C21, "=1")

Use the above syntax to count the number of 1’s in the Number of occurrences column to get the number of unique values in the Make column.

Count Unique Values with an Apps Script Custom Function

Other methods up to this point have needed an extra step or required using more than one function to count the number of unique items in a range.

You can have an easier and simpler process of counting unique items by creating a custom function using an app script.

To open the apps script editor window, go to the Extensions menu and select App script.

function COUNTUNIQUEVALUES(range) {

  var rowHeight = range.length;
  var colWidth = range[0].length;
  allItems = [];
  uniqueItems = [];

  //Loop through selected range of values and add values
  //to 1 dimensional array
  for (let i = 0; i < rowHeight; i++) {
    for (let j = 0; j < colWidth; j++) {
      allItems.push(range[i][j]);
    };
  };

  for (let i = 0; i < allItems.length; i++) {
    if (allItems.indexOf(allItems[i], allItems.indexOf(allItems[i]) + 1) == -1) {
      uniqueItems.push(allItems[i]);
    };
  };

  return uniqueItems.length;
}

Paste the above syntax into the editor, then click on Save and Run. After granting the required permissions, go back to your spreadsheet and refresh it.

= COUNTUNIQUEVALUES ( C6:C21 )

The syntax creates a custom function named COUNTUNIQUEVALUES which only requires a range of cells to return the number of values that appears just once.

Conclusion

You can use a combination of the COUNTA and UNIQUE functions to get the count of unique items.

The ARRAYFORMULA and COUNTIF functions can also be used for another formula method to count the number of unique values in a range.

You can also use app scripts to create a custom function for this purpose. This can be a much simpler route.

Considering how much insight you can get from counting the unique values in your dataset, isn’t it beneficial that you have various methods to do it at your disposal?

Do you find this helpful? Let me know in the comments section!