There are many ways to count unique values in Google Sheets. You can use the Count Unique Values tool, or you can use the Unique Values tool from the Google Sheets website. The Count Unique Values tool is a simple way to count unique values in a sheet. To use the Count Unique Values tool, you first need to create a sheet. You can create a sheet by clicking on the Create Sheet button on the left side of the screen. Once you have created your sheet, you can use the Count Unique Values tool to count unique values. To use the CountUniqueValues tool, you first need to select a value from your sheet. You can select a value by clicking on it and then selecting another value from your sheet. You can also select multiple values from your sheet and then press Ctrl+C to copy all of those values into one column on your sheet. TheCountUniqueValues tool will then show you how many unique values each of those selected values has. If you want to remove any of the selected values from your sheet, you can do that by pressing Ctrl+X and then selecting another value from your sheet.
Counting the number of distinct values in a spreadsheet is valuable in many situations. Whether it’s customer names, product numbers, or dates, one simple function can help you count unique values in Google Sheets.
Unlike Microsoft Excel which has various ways to count distinct values depending on your version of Excel, Google Sheets offers a handy function that uses a basic formula. Luckily, the function works with numbers, text, cell references, inserted values, and combinations of all for complete flexibility.
Use the COUNTUNIQUE Function in Google Sheets
COUNTUNIQUE is one of those Google Sheets functions that you’ll appreciate once you start using it. Save time and manual work counting cells that are unlike the rest.
RELATED: 9 Basic Google Sheets Functions You Should Know
The syntax is COUNTUNIQUE(value1, value2, …) where only the first argument is required. Let’s look at a handful of examples so that you can use the function effectively for different types of data.
To count the number of unique values in the cell range A1 through A16, you would use the following formula:
Maybe you have your own values that would want to insert rather than those displayed within cells. With this formula, you can count the number of unique values you insert:
Here, the result is 4 because values 1, 2, 3, and 4 are unique regardless of how many times they appear.
For this next example, you can count inserted values as shown above combined with values in a cell range. You’d use this formula:
In this case, the result is 5. The numbers 1, 2, and 3 are unique as are the values within the cell range A2 through A3.
If you want to include words as inserted values, the function counts these as unique elements as well. Look at this formula:
The result is 5 because each value in the formula is distinct, whether it’s a number or text.
For the ultimate combination, you can use a formula like this to count inserted values, text, and a cell range:
The result here is 6 which counts the numbers 1, 2, and 3, the text, and the unique values in the range A2 through A3.
Use the COUNTUNIQUEIFS Function for Adding Criteria
Not everything in a spreadsheet is simple. If you like the idea of the COUNTUNIQUE function but would like to count unique values based on criteria, you can use COUNTUNIQUEIFS. The nice thing about this function is that you can use one or more sets of ranges and conditions.
RELATED: How to Count Data Matching Set Criteria in Google Sheets
The syntax is COUNTUNIQUEIFS(count_range, criteria_range1, criteria, criteria_range2, criteria2, …) where the first three arguments are required.
In this first example, we want to count the unique values in the range A2 through A6 where the value in the range F2 through F6 is greater than 20. Here’s the formula:
The result here is 2. Even though there are three values greater than 20 in the F2 through F6 range, the function provides only those that are unique from the range A2 through A6, being Wilma Flintstone and Bruce Banner. The latter appears twice.
Let’s use this function with text criteria. To count the unique values in the range A2 through A6 where the text in the range E2 through E6 equals Delivered, you’d use this formula:
In this case, the result is also 2. Although we have three marked as Delivered, only two names in our A2 through A6 range are unique, Marge Simpson and Bruce Banner. Again, Bruce Banner appears twice.
Like counting the number of unique values in your spreadsheet, highlighting duplicates in Google Sheets or removing duplicates completely is also helpful.
