Calculating the total number of entries in a spreadsheet table is straightforward using standard COUNTA or COUNTIFS functions. However, real-world reporting frequently requires determining the number of unique entities—such as unique customers, distinct product IDs, or individual sales reps—that satisfy specific criteria. While Google Sheets includes COUNTUNIQUEIFS, it has strict range syntax requirements and cannot handle complex calculated criteria, date transforms, or multi-field OR logic. To build flexible, distinct-count metrics, nesting the FILTER function inside the COUNTUNIQUE function provides a versatile, robust solution.
Understanding the Syntax of COUNTUNIQUE and FILTER
The formula pairs Google Sheets’ native deduplication counter with its dynamic array filter:
=COUNTUNIQUE(FILTER(return_range, condition1, [condition2, ...]))How the two functions work together:
- FILTER: Evaluates your dataset rows against one or more criteria conditions and extracts only the matching items from the
return_range. - COUNTUNIQUE: Scans the filtered list of extracted values, eliminates all duplicate occurrences, and outputs the exact count of unique items.
Basic Example: Counting Unique Customers in a Specific Region
Suppose you have sales transaction records where Column A contains Customer IDs and Column B contains Sales Regions. To calculate how many unique customers placed orders in the “North” region:
=COUNTUNIQUE(FILTER(A2:A100, B2:B100="North"))If Customer CUST-104 placed five separate orders in the North region, FILTER extracts five rows containing CUST-104, but COUNTUNIQUE counts them as a single distinct customer.
Multi-Criteria Filtering with AND Logic
To count unique values based on multiple simultaneous conditions, add conditions using multiplication (*) syntax or additional comma arguments inside FILTER.
To count distinct products sold (Column A) where the Region is “West” (Column B) AND the Order Total exceeds $500 (Column C):
=COUNTUNIQUE(FILTER(A2:A100, (B2:B100="West") * (C2:C100>500)))Implementing OR Logic Across Multiple Categories
Standard COUNTIFS or COUNTUNIQUEIFS functions fail when evaluating OR conditions across different categories. With nested FILTER, use addition (+) to evaluate OR statements.
To count unique clients (Column A) who ordered either “Software” OR “Consulting” services (Column B):
=COUNTUNIQUE(FILTER(A2:A100, (B2:B100="Software") + (B2:B100="Consulting")))Filtering by Date Ranges and Months
A frequent business requirement is calculating monthly active unique users from a timestamped activity log. Suppose Column A contains Usernames and Column B contains Transaction Dates.
To count unique users who logged in during October 2026:
=COUNTUNIQUE(FILTER(A2:A100, (B2:B100>=DATE(2026,10,1)) * (B2:B100<=DATE(2026,10,31))))Safely Handling Zero-Match Scenarios with IFERROR
When no records satisfy the specified filter conditions, the FILTER function returns a #N/A error. When wrapped in COUNTUNIQUE, this error bubbles up to display #N/A. To ensure your dashboards display a clean 0 when zero matches occur, wrap the inner FILTER inside IFERROR:
=COUNTUNIQUE(IFERROR(FILTER(A2:A100, B2:B100="Antarctica"), ""))If no matches are found, IFERROR provides an empty string, which COUNTUNIQUE safely evaluates as zero valid items.
COUNTUNIQUE(FILTER()) vs COUNTUNIQUEIFS Comparison
| Capability | COUNTUNIQUE(FILTER()) | COUNTUNIQUEIFS |
|---|---|---|
| Basic AND Conditions | Supported natively | Supported natively |
| OR Logic Across Fields | Fully supported via + operator |
Not supported (requires multiple formulas) |
| Calculated Conditions (e.g. YEAR(date)) | Fully supported | Not supported; requires hardcoded cell range |
| External Workbook Ranges | Compatible with IMPORTRANGE |
Fails on external dynamic arrays |