How to Count Unique Values in Google Sheets

Let’s say you manage a small team and keep track of their weekly tasks in Google Sheets. Each team member’s name is listed in Column A, and you want to know how many unique tasks each member handles. To do this, you need to count the unique values in Column B, where tasks are listed.

In this tutorial, we will learn all the methods to count unique values from data. I have found that counting unique values from a list in Google Sheets where you have duplicate values is quite easy compared to Microsoft Excel.

• There is a specific function called COUNTUNIQUE for this.
• And there’s also a COUNTUNIQUEIF to count unique values with a condition.
• You can also combine COUNTA and UNIQUE functions.
• You can also write a Google App script for a custom function.
• You can even combine SUMPRODUCT and COUNTIF to write a formula.

Now, let’s get started…

COUNTUNIQUE for Counting Unique Values

This function is why I said that Google Sheets has a better way of counting unique values than Excel. When you enter this function, you need to refer to the range where you have the values and then hit enter to get count in the cell.

=COUNTUNIQUE(B2:B11)

Here, COUNTUNIQUE scans the range B2:B11 to identify all distinct values within this range. It counts each unique value only once, ignoring any duplicates. In this case, it counts six unique tasks, each appearing at least once in the specified range.

Count Unique Values with a Condition

It amazed me to see that this function exists in Google Sheets. COUNTUNIQUEIFS can count the number of unique values that meet one or more specified criteria. It combines the functionality of COUNTUNIQUE and IFS

COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• range: The range from which to count unique values.
• criteria_range1, criterion1: The range and criterion for the first condition.
• criteria_range2, criterion2, …: Additional ranges and criteria (optional).

Now, from the list of tasks, you want to count the unique tasks assigned to “Alice”. From this, you need to write the formula below:

This formula counts the tasks in column B, where the corresponding name in column A is “Alice,” and then counts the unique tasks from that list.

For this, it first checks column A to identify rows where the name is “Alice.” It then looks at the corresponding cells in the task range to find Alice’s unique tasks. In the end, it counts the unique tasks from that list.

Count Unique Values with COUNTA and UNIQUE

This method combines COUNTA and UNIQUE functions where UNIQUE returns a list of unique values from the range, removing duplicates, and then COUNTA counts those values to return the count of unique values.

=COUNTA(UNIQUE(B2:B11))

Now, to write a formula using both functions, follow the steps below:

1. Enter the COUNTA function and then enter the UNIQUE function within it.
2. Now, refer to the range where you have tasks. In our example, we have column B, where we have a list of the tasks.
3. After that, close the UNIQUE function and the COUNTA function.
4. In the end, hit enter to the result in the cell.

Here, UNIQUE generates a list of unique tasks from B2 to B11. It scans the range and removes duplicate tasks, leaving only one instance of each unique task. And then, COUNTA gets the count of those tasks in the result.

Google App Script to Show Message Box to Show Unique Count

Below is the code that you can use to get a count of unique values from the selected cells. To use this code, go to the “Extension” and “App Script”. Paste the code and then save it. After that, run this code to get the custom menu in the menu bar.

Now select a range and then click on “Count Unique Values” to get the count in a message box, like below:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Count Unique Values', 'countUniqueValues')
    .addToUi();
}

function countUniqueValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var values = range.getValues();
  var uniqueValues = [];

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (uniqueValues.indexOf(values[i][j]) === -1 && values[i][j] !== '') {
        uniqueValues.push(values[i][j]);
      }
    }
  }

  var ui = SpreadsheetApp.getUi();
  ui.alert('Unique Values Count: ' + uniqueValues.length);
}

Create an Array Formula to Getting Unique Values with SUMPRODUCT

With SUMPRODUCT, you can create an array formula. And you need to use COUNTIF with that. Writing this complex formula works impressively when you understand it.

However, as we already have a specific function in Google Sheets, you might not need to use it. That is why I have shared this method in the end to have low priority.

=SUMPRODUCT(1/COUNTIF(B2:B11, B2:B11&""))

In this formula, COUNTIF(B2:B11, B2:B11&””), counts how many times each value in B2:B11 appears within the same range. And when you use B2:B11 & “”, it ensures that all values are treated as text, which helps avoid errors with different data types.

By dividing 1 by the count of each value, we effectively get a fraction representing each occurrence’s contribution towards the unique count. Say, if a value comes 3 times, it makes it 1/3 = 0.333333333, or for twice it makes it 0.5.

In the end, SUMPRODUCT sums up all these fractions. Since each unique value contributes 1 (whether it appears once, twice, or more), the sum represents the total number of unique values in the range.

Important Points

Here are a few important points to take while using the methods we have learned.

• Hidden characters or extra whitespace can cause duplicate entries to be counted as unique. =COUNTUNIQUE(TRIM(B2:B11))
• Empty cells in the range can cause unexpected results. Use a formula that handles empty cells easily. =COUNTUNIQUE(FILTER(B2:B11, B2:B11 <> “”))
• Counting unique values across non-contiguous ranges can be a little tricky. You can combine ranges using the ARRAYFORMULA and UNIQUE functions. =COUNTA(UNIQUE(ARRAYFORMULA({B2:B5; B7:B11}))).
• You can also use multiple conditions to get the count of unique values. =COUNTUNIQUEIFS(B2:B11, A2:A11, “Alice”, C2:C11, “Completed”)