Using the COUNTUNIQUEIFS function, we can conditionally count unique values in a data range in Google Sheets. This is a relatively new Google Sheets function, that allows you to count the number of unique values in a range based on whether conditions in other ranges are met.
To conditionally count unique values, earlier I used the COUNTUNIQUE function in combination with the FILTER function. In this tutorial, I have included that earlier method as well.
Here are the syntax and arguments of the COUNTUNIQUEIFS function in Google Sheets.
Syntax:
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
Arguments:
range
: The range or array from which the number of unique values is to be counted.criteria_range1
: The range or array over which to evaluate thecriterion1
.criterion1
: The pattern or test to apply tocriteria_range1
.
Other arguments are optional and repetitions of the above two arguments.
I understand these arguments might be confusing. Don’t worry. I will try to elaborate on these in the formula section below.
Example of COUNTUNIQUEIFS Function in Google Sheets
Assume three individuals participated in an athletics event, specifically the Long jump, and their performances (distance jumped) have been recorded in Google Sheets. Each person has three scores as they jumped thrice.
Out of curiosity, I want to find the count of unique (distinct) performances for each individual. Let’s see how to achieve this using a COUNTUNIQUEIFS formula in Google Sheets.
The athletes’ names (criteria_range1
) are in cells A2:A and their distances jumped (range)
are in cells B2:B. In cell E2, enter any athlete’s name (criterion1
) and use the following formula in cell F2:
=COUNTUNIQUEIFS(B2:B, A2:A, E2)
As you can see, the formula returns 2 as the output when the criterion1
in cell E2 is “John”.
When you check the performances of “John”, you can find the distance jumped as 8.2 m, 8 m, and 8.2 m. The unique count of these values is 2.
This is an example of a conditional count of unique values using the COUNTUNIQUEIFS function in Google Sheets.
Earlier Approach
Before the introduction of COUNTUNIQUEIFS, we were using a combination of COUNTUNIQUE + FILTER for the conditional count of unique values in Google Sheets.
In the above example, we can use this formula as an alternative.
=COUNTUNIQUE(FILTER(B2:B, A2:A=E2))
Multiple Criteria in COUNTUNIQUEIFS Function in Google Sheets
In the above basic formula approach, we have applied a condition in a single column, specifically in the range containing the athletes’ names.
However, sometimes we may need to apply more than one condition, which can be in the same column or in two or more different columns.
Let’s see how to address that in the COUNTUNIQUEIFS function in Google Sheets.
Applying OR Logic (Two Criteria in a Range)
In the above example, we have performed a conditional count of unique values based on one athlete’s name. But what if we want to include two athletes’ names?
The COUNTUNIQUEIFS function does not directly support applying an OR logic (this or that) in a column. We need to follow a workaround that involves the MATCH function.
Problem: Return the count of unique performances of the athletes John and Andie.
Formula:
=COUNTUNIQUEIFS(B1:B, ArrayFormula(MATCH(A1:A, {"John", "Andie"}, 0)^0), 1)
Where:
range
: B1:Bcriteria_range1
:ArrayFormula(MATCH(A1:A, {"John", "Andie"}, 0)^0)
criterion1
: 1
Formula Breakdown:
=ArrayFormula(MATCH(A1:A, {"John", "Andie"}, 0))
This formula will return relative positions (John as 1 and Andie as 2) in matching rows and #N/A in other rows. You can try it in cell D2 after emptying D2:D.
We need to convert all the relative positions to the same number.
=ArrayFormula(MATCH(A1:A, {"John", "Andie"}, 0)^0)
This will return 1 in matching rows and #N/A in other rows.
With this, what we have done is manipulate the criteria range to apply one condition, and that condition will be 1.
Applying AND Logic (Two Criteria Ranges)
I have a sample dataset where column A contains fruit names, column B contains the quantity ordered, and column C contains the delivery status.
I want to find the unique count of fruits with quantities greater than 100 and the status “delivered”. This means we need to meet two conditions in the conditional count of unique values.
Formula:
=COUNTUNIQUEIFS(A2:A, B2:B, ">100", C2:C, "Delivered")
What if the criterion is a date?
In the COUNTUNIQUEIFS formula, you can use the date criterion as shown in the following example where the syntax of the DATE function is DATE(year, month, day)
:
">"&DATE(2019, 8, 14)
Resources
Here are some related resources.
- Row-Wise COUNTUNIQUEIFS in Google Sheets (Array Formula)
- How to Use COUNTIF with UNIQUE in Google Sheets
- Count Unique Values in Visible Rows in Google Sheets
- Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets
- Counting Unique Values in Multiple Columns in Google Sheets
- Count Unique in Google Sheets QUERY
How to use multiple criteria from the same column in the COUNTUNIQUEIFS formula?
I want to count 2 different values from 1 column and put time criteria from the other column.
Hi, Dhananjai Kumar,
You may please try to share a sample sheet below in your reply.
Hi Prashanth
I wanted to know why Countuniqueifs formula doesn’t expand when used with ArrayFormula like other functions such as IFS or SUMIF etc.
I have three columns. Col A is for dates, Col B is for operators and Col C is the product they have handled. There are almost 15 to 20 entries with the same date and the operator handled 3 to 4 different products.
I wanted to know the number of operators worked on a particular date. I have used a Unique formula to get the dates and using Countuniqueifs formula to get the number of operators present for that day.
It works when used in a single cell but doesn’t expand when used with an array formula.
Any suggestion?
Hi, Aditya Darekar,
Similar to SUMIFS it seems the COUNTUNIQUEIFS won’t work with the ArrayFormula to expand. You can try this Unique and Query combination.
=query(unique(A1:B),"Select Col1,count(Col2) where Col1 is not null group by Col1")
Thanks, Prashanth,
I have tried but it is not giving me the unique values count rather it is giving me total count.
Can you share your sheet, please? The link will be safe (I won’t publish it). Only share a mockup sheet.
Thanks a lot, Prashanth… It is working.
Actually, later on, I have added one more column for Item name in Col B so my column B shifted to Col C. So I just altered in the formula to C and Col3.
=query(unique(A1:C),"Select Col1,count(Col3) where Col1 is not null group by Col1")
But didn’t work.
Now just changed to
=query(unique({A1:A,C1:C}),"Select Col1,count(Col2) where Col1 is not null group by Col1")
IT WORKED!!!!
Hi, Aditya Darekar,
That’s because of the Unique().
Thanks for your feedback!
Hello Prashanth,
Thank you for your tutorials they have been very helpful. You have given an example for date criterion within the formula, however, what if the dates are part of the table?
Let’s say in your fruit table example your columns B and C were actually two dates. In a separate cell, I’d like to enter a date for which unique fruits in column A are counted if the date entered is between the 2 dates given in columns B and C. Is this still possible using the COUNTUNIQUEIFS function?
Thanks!
Hi Pablo,
Assume the date criterion for COUNTUNIQUEIFS is in cell E2. Then the formula would be as follows.
Date range in Countuniqueifs function:
Best,