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)

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")

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