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
Table of Contents
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, ...])- count_unique_range: Range from which unique value needs to be counted
- criteria_range1: range against which criterion1 will be evaluated
- 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:
- All Ranges(count_unique_range or criterion_range1) must be of same length
- You can use all types of comparision operators like >,<,>=,<=.=,<> when applying numerical criteria
- You can apply wildcard characters while applying Text Criterions
- 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:-
- Numbers(Integers, Decimals & % included)
- Date, Time, DateTime values
- Text
- Boolean(TRUE/FALSE)
- Errors
Point to note:
- In case there is a repetition of values then it count will happen only once
- If criterion_range fails to meet the criterion then corresponding value in count_unique_range will be ignored
- 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.
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).
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.
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.
That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂
