In data analytics, reporting duplicate transactions is often misleading. Businesses routinely need to determine how many unique customers made purchases, how many distinct products were ordered in a specific territory, or how many individual employees clocked in during a shift. While standard COUNTIFS counts the total number of rows matching criteria (including repeated entries), it cannot distinguish duplicates. Historically, Google Sheets analysts combined COUNTA, UNIQUE, and FILTER to calculate deduplicated subsets. Recognizing this frequent requirement, Google introduced the native COUNTUNIQUEIFS function, delivering direct, high-performance unique item counting with multiple criteria filtering in a single formula.
What Is the COUNTUNIQUEIFS Function?
The COUNTUNIQUEIFS function counts the number of unique (distinct) values within a target range that satisfy one or more filtering conditions across associated columns.
Formula Syntax
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])The components of the formula operate as follows:
range: The column or array containing the values you want to count uniquely (e.g.A2:A100).criteria_range1: The first range of cells to evaluate against your condition (e.g.B2:B100).criterion1: The filter rule, text string, number, or cell reference that must be matched (e.g."Completed"or">=500").[criteria_range2, criterion2, ...](Optional): Additional range and condition pairs. All criteria must evaluate to TRUE (AND logic) for a row to be included.
Practical Example: E-Commerce Customer Analytics
Suppose you maintain an order log in cells A1:D15:
- Column
A: Customer ID (e.g. C101, C102, C101, C103) - Column
B: Region (e.g. Europe, North America) - Column
C: Order Status (e.g. Delivered, Cancelled) - Column
D: Order Value (e.g. 150, 400)
Example 1: Counting Unique Customers in a Specific Region
If customer C101 placed five separate orders in Europe, a standard COUNTIFS counts 5. To find how many distinct customers made purchases in Europe:
=COUNTUNIQUEIFS(A2:A15, B2:B15, "Europe")Google Sheets filters for rows where Region is Europe, deduplicates the Customer IDs, and returns the exact unique customer tally.
Example 2: Multiple Criteria (Region and Order Status)
To count how many unique customers in North America have orders marked as Delivered:
=COUNTUNIQUEIFS(A2:A15, B2:B15, "North America", C2:C15, "Delivered")Example 3: Using Comparison Operators with Numerical Thresholds
You can incorporate mathematical operators (>, <, >=, <=, <>) to filter by values. To count distinct customers who completed transactions exceeding 250:
=COUNTUNIQUEIFS(A2:A15, C2:C15, "Delivered", D2:D15, ">250")Dynamic Criteria Referencing External Cells
In dashboard design, hardcoding strings into formulas makes spreadsheets brittle. You can reference adjacent user input cells:
Suppose cell F1 contains the target Region and cell F2 stores the target Status. In cell F3, enter:
=COUNTUNIQUEIFS(A2:A15, B2:B15, F1, C2:C15, F2)When selecting a numerical threshold stored in cell F4, concatenate the comparison operator with an ampersand (&):
=COUNTUNIQUEIFS(A2:A15, D2:D15, ">=" & F4)Comparison: COUNTUNIQUEIFS vs Legacy Formulas
| Method | Formula Syntax | Performance & Readability |
|---|---|---|
| COUNTUNIQUEIFS | =COUNTUNIQUEIFS(A2:A, B2:B, "East", C2:C, "Won") |
Fast, native C++ evaluation in Sheets engine, highly readable. |
| COUNTA + UNIQUE + FILTER | =IFERROR(COUNTA(UNIQUE(FILTER(A2:A, B2:B="East", C2:C="Won"))), 0) |
Verbose, requires IFERROR wrapper to prevent #CALC! errors on zero matches. |
Adopting COUNTUNIQUEIFS substantially reduces spreadsheet formula complexity, prevents reference errors, and accelerates calculation speeds when compiling KPI dashboards in Google Sheets.