How to Use the COUNTUNIQUEIFS Function in Google Sheets to Count Unique Values with Criteria

Analysing transactional data, customer inventories, or operational logs in Google Sheets frequently requires determining how many distinct entities met specific operational conditions. While the traditional COUNTIFS function counts every matching row, it duplicates entities that appear repeatedly in the register. Conversely, combining COUNTA(UNIQUE(FILTER(...))) can result in verbose, fragile nested formulas. The native COUNTUNIQUEIFS function resolves this challenge directly by aggregating distinct, non-duplicate values across a target range filtered by one or more conditional rules.

Syntax and Parameter Breakdown

The COUNTUNIQUEIFS function calculates the number of unique items across a specified target range, evaluating one or more filtering pairs before calculating uniqueness:

=COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• range: The primary range containing the items you wish to count uniquely (for example, Customer IDs, SKU codes, or email addresses).
• criteria_range1: The first range to evaluate against your conditional filter. This range must possess the exact same row or column dimensions as the primary range.
• criterion1: The condition, expression, cell reference, or text string that determines which rows in criteria_range1 are eligible for unique aggregation.
• [criteria_range2, criterion2, ...] (Optional): Additional range-and-criterion pairs. All criteria must simultaneously evaluate to TRUE (logical AND behaviour) for a value to be included in the unique tally.

Single-Condition Unique Counting

Suppose you maintain an order log where Column A lists Customer ID (e.g., A2:A100), Column B contains the Order Status (B2:B100), and Column C contains the Order Value (C2:C100). Several customers have placed multiple orders throughout the month.

To count the number of distinct customers who have placed orders with a status of “Delivered”:

=COUNTUNIQUEIFS(A2:A100, B2:B100, "Delivered")

If Customer CUST-104 completed four separate orders marked “Delivered”, Google Sheets counts CUST-104 only once within the calculation.

Multi-Condition Filtering with Dates and Numerical Thresholds

To evaluate complex reporting queries across multiple dimensions, append subsequent criteria ranges and logical operators directly into the parameter sequence.

Example 1: Filtering by Region and Minimum Spend

To count distinct account managers in Column A (A2:A50) who closed deals in the “EMEA” region (Column B, B2:B50) exceeding £5,000 (Column C, C2:C50):

=COUNTUNIQUEIFS(A2:A50, B2:B50, "EMEA", C2:C50, ">5000")

Example 2: Date Range Boundaries with Dynamic Cell Referencing

When filtering transactions occurring between two specific calendar dates entered in cells F1 (Start Date) and F2 (End Date), concatenate the comparison operators with the cell references:

=COUNTUNIQUEIFS(A2:A200, B2:B200, "Completed", D2:D200, ">=" & F1, D2:D200, "<=" & F2)

Handling Wildcards and Partial Text Matches

COUNTUNIQUEIFS supports standard spreadsheet wildcard characters within text-based criteria expressions:

• Question Mark (?): Matches any single character. For example, "PROD-??" matches codes like PROD-01 and PROD-9A, but ignores PROD-001.
• Asterisk (*): Matches any sequence of characters. For example, to count unique users whose registered department in Column B ends with “Operations”:

=COUNTUNIQUEIFS(A2:A100, B2:B100, "*Operations")

To search for an actual literal question mark or asterisk, prepend a tilde character (e.g., "~?" or "~*").

COUNTUNIQUEIFS vs COUNTIFS and UNIQUE+FILTER

Understanding when to implement COUNTUNIQUEIFS versus legacy alternatives optimises workbook performance and formula maintenance:

• COUNTIFS: Returns the total count of all matching instances, including duplicates. Use this when calculating raw transaction volume rather than distinct participants.
• COUNTUNIQUEIFS: Delivers instant distinct counting within a single native calculation. It eliminates duplicate entities natively while applying multiple simultaneous filters.
• COUNTA(UNIQUE(FILTER(...))): Useful if you need to extract the actual array of unique values onto the sheet for downstream referencing. However, if no rows meet the criteria, FILTER returns an #N/A error requiring extra IFERROR handling, whereas COUNTUNIQUEIFS cleanly outputs 0.

Common Troubleshooting and Calculation Gotchas

If your formula outputs unexpected results or formula errors, check these common configuration discrepancies:

• Mismatched Range Dimensions: Every criteria range passed into the function must match the row count and column count of the first evaluation range. If range is A2:A100 and criteria_range1 is set to B2:B99 or B1:B100, Google Sheets triggers an #ARG! error.
• Case Insensitivity: COUNTUNIQUEIFS evaluates text criteria in a case-insensitive manner. Values like “LONDON” and “London” are treated as identical unique tokens unless transformed through auxiliary helper arrays.
• Blank Cell Behaviour: Empty cells in the primary counting range are not tallied by COUNTUNIQUEIFS. If you explicitly want to exclude rows where criteria columns are blank, add a condition such as B2:B100, "<>".