How to Use the COUNTUNIQUEIFS Function in Google Sheets

Published on

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 the criterion1.
  • criterion1: The pattern or test to apply to criteria_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)
Basic Example of COUNTUNIQUEIFS Function in Google Sheets

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:B
  • criteria_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")
Multiple Criteria in COUNTUNIQUEIFS

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

10 COMMENTS

  1. 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.

  2. 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.

          • 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!!!!

  3. 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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.