Counting is a very fundamental operation in any spreadsheet activity and fortunately Google Sheets provides us with a plethora of count related function to work with. COUNTUNIQUE is one such type of function where your sole objective is to count all distinct values within a supplied group of cells.
So when will you need it? You will need it everytime when the group of cells you are looking at has repetitions and all you want is distinct count of unique values.
COUNTUNIQE is an operational easy function to execute but still has nuances one should be aware of. In this tutorial we’ll look to explore all aspects of COUNTUNIQUE Function
Table of Contents
Purpose
Provides the count of all unique values within a supplied dataset.
Syntax and Parameter Definition
= COUNTUNIQUE(value1, [value2, value3, .....])- value1: Can be a constant value(3,2,4 etc), cell reference(A2,B3 etc) or range(B1:B10) or a formula output which will be considered for counting purposes.
Rest of the arguments have the same definition as of value1, however, they are optional to supply
Note: Although, Google Sheet Specifies to only have a max of 30 inputs, you can go above that limit if required.
Expected Output
COUNTUNIQUE Function counts everything apart from blank/empty cells. Below is the laundry list of all data elements which can be counted:-
- Numbers(Integers, Decimals & % included)
- Date, Time, DateTime values
- Text
- Boolean(TRUE/FALSE)
- Errors
Also, if there’s repetition of any value then COUNTUNIQUE Counts it just once.
Note: Text comparisons are case-sensitive. Hence, TOM and Tom will not be treated as same and will be counted twice.
Examples
In the following sub-sections we have demonstrated examples to cover all the different aspects of COUNTUNIQUE Function.
COUNTUNIQUE Function – Basic Example
As we can see below, we have 1 to 4 listed in B3:B10, each repeated twice. When COUNTUNIQUE is applied over the on this range we get an output of 4. Which is what was expected too.
COUNTUNIQUE Function – Working with Different Data Types
As stated earlier COUNTUNIQUE can count values like dates, text etc too. In the below example we have highlighted how Countunique can be used across different data types individually(1-4) or in combination(5-6).
Similar values have been highlighted using same color for easy read.
Note: The final output is very much consistent with the definition of the formula. All we are getting is no of distinct values as output.
COUNTUNIQUE Function – Different Combination of Input types
The input supplied into COUNTUNIQUE Function in the previous example was a range(B3:B12). But it doesnt have to be that way. You can always provide static inputs, cell references and range in any combination you desire.
Below example is a good demonstration of this fact.
COUNTUNIQUE Function – Case of blank/empty cells
COUNTUNIQUE Ignores any blank/empty cells coming it’s way. But it’s not as strightforward.
Values like Single Space Characters ” “, or empty string “” or apostrophe also look empty(see below). However, amonst these, Single Space Characters will be counted.
Visual Demo of COUNTUNIQUE Function
Before we end here is a sample visual demonstration.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
