In this tutorial, we will learn How to count unique values in Google Sheets.
- Whenever you work with large datasets in Google Sheets. You would like to count the unique values in Google Sheets.
- In Google Sheets, you can quickly count unique values using an in-built function.
You can count unique values in Google Sheets following the below methods.
- Using COUNTUNIQUE
- Using COUNTIF with UNIQUE
How to Count Unique Values in Google Sheets : Step by Step Guide
How to Use COUNTUNIQUE
- The simplest approach to count unique values in Google Sheets is by using the COUNTUNIQUE function.
- This function counts the number of unique values in your selected range.
Syntax for COUNTUNIQUE
COUNTUNIQUE(value1, [value2, ...])
- value1 – It is the range of value or the first value to check for uniqueness.
- value2 – value2 is optional which you can use to provide additonal range for you data to check for uniqueness.
Enclosed are the steps to use COUNTUNIQUE to count unique values in Google Sheets:
- Click on an empty cell and type the formula =COUNT and select COUNTUNIQUE from the drop-down in auto -suggest .
- Now Select the data Range from the auto-suggest where Google Sheet automatically selects and suggests the data range .In example =COUTUNIQUE(A1:A23)
- Click on Enter.It will count the unique value in your data range.
You can also type the complete formula =COUNTUNIQUE and followed by open parenthesis “(“ and then select the data range for which you want to count the unique values followed by close parenthesis “)” and then click on Enter.
- In the above example, the entire range of dataset from A1 to A23 was selected which returned a value of 21. Hence we have 21 unique values in our data range.
- Apart from numbers, the COUNTUNIQUE function can also be used with text data, multiple value ranges, and strings.
Following are the examples to use the COUNTUNIQUE function with text data, multiple value ranges, and strings.
You can use the function specified in Column C without quotes to get the result.
How to Use COUNTIF with UNIQUE
If you have a requirement where you need to count the unique values and want to know how many times the unique data appears in your data range, COUNTIF with UNIQUE can be useful in this scenario.
In the preceding example, we have calculated only the unique values using COUNTUNIQUE.
- Using COUNTIF with UNIQUE not only calculates the unique values it also return the number of appearance for a value in provided range.
- If we use COUNTIF with UNIQUE we can return the unique values and count the number of times each value appears in the selected range.
We’ll need three functions to execute this formula: COUNTIF, ARRAYFORMULA, and UNIQUE.
- COUNTIF – It will calculate and return a conditional count in the selected range
- ARRAYFORMULA – Allows the display of array formula values in many rows and/or columns, as well as the usage of non-array functions with arrays.
- UNIQUE – It will calculate and return only unique values ignoring duplicates
- Returns only unique rows from the given source range, ignoring duplicates.
Following is the Syntax to use the formula
{UNIQUE(A2:A24),ARRAYFORMULA(COUNTIF(A2:A24,UNIQUE(A2:A24)))}
Below is the example of the returned result with the above formula:
The preceding function calculates and provides a unique value as result in the Unique value column and the number of occurrences for unique value in Column C.
Conclusion
In this tutorial, we have gone through the usage of COUNTUNIQUE
and COUNTIF with a UNIQUE function.
We hope this tutorial on How to count unique value in Google Sheets was helpful.
Related articles :
Google Sheets Formulas Not Updating ( Easy Fix )
How to Add or Remove Dollar Sign in Google Sheets ( Easy Guide )
How to use Median IF function in google sheets ( Easy Guide )
