COUNTUNIQUE Function in Google Sheets

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

Purpose

Provides the count of all unique values within a supplied dataset.

Syntax and Parameter Definition

= COUNTUNIQUE(value1, [value2, value3, .....])
  1. 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:-

  1. Numbers(Integers, Decimals & % included)
  2. Date, Time, DateTime values
  3. Text
  4. Boolean(TRUE/FALSE)
  5. 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.

Google Sheet Examples

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.

Google Sheet Examples

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.

Google Sheet Examples

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.

Blank inputs to COUNTUNIQUE Function

Visual Demo of COUNTUNIQUE Function

Before we end here is a sample visual demonstration.

COUNTUNIQUE Demo - Google Sheets

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply