Do you need to count the distinct values in your Google Sheets data?
Counting distinct values helps you to identify the number of different items in a selected range of cell values quickly and easily. But other benefits come with counting the distinct values in your dataset.
When you count distinct values, you can get to know the level of diversity present within your dataset.
In addition to this, counting distinct values helps you to identify errors, duplicates, and erroneous entries that can reduce the quality of your data. You can even identify trends and patterns by counting distinct values.
Considering how much significant information you can glean from counting distinct values in your dataset, it’s important to know how to perform this task in Google Sheets.
In this post, you will see all the different ways you can count distinct values in your dataset.
What’s the Difference Between Distinct and Unique?
Distinct and Unique are concepts that can be easily confused. However, here’s a simple explanation to clear any confusion.
When you count distinct values, you’re identifying the number of different items that exist within a given range. Unique count on the other hand refers to the number of items or values that appear only once within a range.
Given an array of letters such as {a, b, c, c, d, e, f, a, b}
.
- A unique count will return 3 since the unique set from these values is
{d, e, f}
. - A distinct count will return 6 since the distinct set from these values is
{a, b, c, d, e, f}
.
📝 Note: Check out this post if you are looking to count the unique values in a range.
Count Distinct with the COUNTUNIQUE Function
The COUNTUNIQUE function returns the number of distinct values in a selected range.
=COUNTUNIQUE(value1, [value2, …])
COUNTUNIQUE has one required argument value
. The argument points to the range of cells containing the values to count. The range can be single or multiple columns, or a combination of text, numbers, or formulas.
= COUNTUNIQUE ( B5:B20 )
The above formula syntax will return the number of distinct values in the Make column.
Count Distinct with the COUNTA and UNIQUE Functions
The UNIQUE function extracts and returns from a range of cells all values that appear only once.
It can also be used to return and remove duplicates. UNIQUE is an array formula, therefore you only need to enter the formula in the first cell and the array will spill into the adjacent cells.
= 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 therange
by row. Theby_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 an optional argument that takes a true or false. With this argument, you can make the function return values that appear just once (unique) or values that appear more than once (distinct). The argument is set to false by default.
= UNIQUE ( B4:B19, false, false )
When you use the above formula syntax, it will return a list of all the different or distinct values that exist in the range.
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 distinct values in the range, wrap the UNIQUE syntax in the COUNTA function.
= COUNTA ( UNIQUE ( B4:B19, false, false ) )
When you wrap the UNIQUE function syntax from earlier in the COUNTA function, you will get the number of all distinct values in the range.
Conclusion
Using the COUNTUNIQUE function or a combination of the COUNTA and UNIQUE functions provides you with an easy and efficient way to determine the number of unique values from a range in your data.
Whether you are working with small or large datasets, these methods will help you quickly analyze and understand the information in your spreadsheets.
The COUNTUNIQUE function is a slightly easier implementation since it’s purpose-built for the task, but does use incorrect terminology.
Do you think you will find this helpful? Let me know in the comments!
0 Comments