COUNTUNIQUEIFS Function in Google Sheets

COUNTUNIQUEIFS in the truest sense is a hybrid of COUNTUNIQUE & COUNTIFS, literally bringing the best of both worlds.

(Highly recommended to have a look at above two articles before going through this one)

As you would have already guessed by the name, COUNTUNIQUEIFS helps to count unique values in a given range basis a given criteria(‘s).


In this tutorial we’ll look to explore all aspects of COUNTUNIQUEIFS Function

Purpose

Returns distinct count within a range based on multiple criteria’s

Syntax and Parameter Definition

= COUNTUNIQUEIFS(count_unique_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
  1. count_unique_range: Range from which unique value needs to be counted
  2. criteria_range1: range against which criterion1 will be evaluated
  3. criterion1: the criteria which will be used to evaluate criteria_range1

You can supply more pairs of range and criteria as per need.

Points to Note:

  1. All Ranges(count_unique_range or criterion_range1) must be of same length
  2. You can use all types of comparision operators like >,<,>=,<=.=,<> when applying numerical criteria
  3. You can apply wildcard characters while applying Text Criterions
  4. Inputs can be static values, cell references or range

Expected Output

COUNTUNIQUEIFS gives the count of unique data elements(apart from blank/empty cells) provided in a given range satisfying a given criteria. Below is the laundry list of all data elements eligible for counting:-

  1. Numbers(Integers, Decimals & % included)
  2. Date, Time, DateTime values
  3. Text
  4. Boolean(TRUE/FALSE)
  5. Errors

Point to note:

  1. In case there is a repetition of values then it count will happen only once
  2. If criterion_range fails to meet the criterion then corresponding value in count_unique_range will be ignored
  3. Text comparisons are case-sensitive. Hence, TOM and Tom will not be treated as same and will be counted twice

This might all sound like too complex because actually it is. Let’s break it down using examples.

Examples

In the following sub-sections we have demonstrated examples to cover all the different aspects of COUNTUNIQUEIFS Function.

Basic Example of Countuniqueifs function

Say, we have the below data of stocks(A,B,C,D) buy and sell during a given day and the trading price. Now, we want to know how many stocks were traded(buy/sell) for over $100 during the day.

COUNTUNIQUEIFS Example

Clearly , there were 4 instances when shares were either bought or sold for over $100, represented by highlighted cell.

Now if we look closely, although the transaction was done 4 times, only 3 stocks A, B and D were traded in those transactions.

Hence 3, is the final output we were looking for which is also provided by COUNTUNIQUEIFS.

Now lets looks closely at how we arrived at this result using COUNTUNIQUEIFS Function. Below is the formula that was used.

=COUNTUNIQUEIFS(B3:B13,D3:D13,">100")

The first parameter B3:B12 is nothing by the column B indicating stock names. Second parameter, D3:D12(criterion_range1) is the corresponding trading price and the third parameter(criterion1) “>100” is the criteria over which D3:D12 will be tested.

Multiple Criteria using COUNTUNIQUEIFS in different column

Life is never that straightforward so are spreadsheets. 😅

More often than not you may end up requiring using multiple criteria’s. We can very well improvise upon previous example to provide a demo.

We want to count number of shares have a selling price over a value of $80. To achieve this we need to add an extra criteria that column C should take value of “Sell”.

We’ll use the below formula to achieve the results.

=COUNTUNIQUEIFS(B3:B13,D3:D13,">100",C3:C13,"Sell")

The output of 2 is as per expectations(A&D).

COUNTUNIQUEIFS Example

Applying Multiple Criteria on Same Range using Countuniqueifs

Say, we just want to count number of shares traded(buy/sell) between 80&120. Here, we need to apply two criteria’s, both on range D3:D13.

If you look closely to the formula, we have added the two criteria over the same range, D3:D13.

COUNTUNIQUEIFS Example

Treatment of Blank/Empty Cells in Countuniqueifs function

In the previous example, i’ll delete B4 and B13.

What happens? The new result is 1.

Why so? – COUNTUNIQUEIFS will ignore blank/empty cells while checking the count.

So, always make sure you dont have empty cells else they would distort the output.

Visual Demo of COUNTUNIQUE Function

Before we end here is a sample visual demonstration.

COUNTUNIQUEIFS Example - Demo

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.

Liked the content?? Then share the word!!!

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