CountUniqueIFS in Google Sheets: Practical Step-by-Step Guide
Master COUNTUNIQUEIFS in Google Sheets with practical, step-by-step examples. Count unique values across multiple criteria, optimize performance, and avoid common pitfalls with hands-on templates from How To Sheets.
Countuniqueifs google sheets counts distinct values in a single range that meet multiple criteria. It combines COUNTUNIQUE with IF-style filtering to return the number of unique items that satisfy all given conditions. The syntax is COUNTUNIQUEIFS(count_range, criterion_range1, criterion1, [criterion_range2, criterion2, ...]). This function is ideal for analyzing filtered datasets and reporting unique results by segment.
What countuniqueifs google sheets does
Countuniqueifs google sheets counts distinct values in a data range while applying multiple filters, returning the number of unique items that match all the given criteria. This is especially useful when you need to know, for example, how many unique customers bought a particular product within a region and date range. According to How To Sheets, this function bridges the gap between simple counts and deep, filtered analyses. The How To Sheets team found that users routinely combine COUNTUNIQUE with IF-style filtering to extract clean, segment-specific insights from noisy datasets. To see this in action, place a formula in any empty cell and reference your data ranges. The next sections will walk you through syntax, examples, and edge cases.
=COUNTUNIQUEIFS(A2:A100, B2:B100, "West", C2:C100, 2026)Note: The example uses a count_range of A2:A100 and two criteria ranges (B2:B100 and C2:C100). You can add more criteria pairs as needed.
If you keep a clean data schema, this pattern scales to larger datasets without manually filtering.
Steps
Estimated time: 10-20 minutes
- 1
Identify target data
Locate the data ranges you will use for count_range and the criterion ranges. Ensure each range has the same length and align by row so that each row represents a single record.
Tip: Label ranges clearly (e.g., SalesAmount, Region, Year) to avoid confusion. - 2
Write the initial formula
Start with a simple COUNTUNIQUEIFS that uses one criterion range. This verifies the basic behavior before adding more complexity.
Tip: Test with a small sample to confirm correct counts. - 3
Expand with additional criteria
Add more criterion pairs to filter on multiple dimensions (e.g., region and year). Keep ranges aligned and verify each additional criterion narrows results as expected.
Tip: If you add a criterion_range that includes text, ensure criteria are quoted or concatenated properly. - 4
Validate results
Cross-check the result with a manual subset check or an alternative method (e.g., FILTER + UNIQUE) to confirm accuracy.
Tip: Use IFERROR to handle empty results gracefully. - 5
Create a reusable template
Turn your working formula into a template by name-ranging the inputs and documenting expected criteria for future analyses.
Tip: Document each parameter so teammates can reuse the same template reliably.
Prerequisites
Required
- Required
- A dataset with at least three columns: count_range, criterion_range1, criterion_range2 (optional)Required
- Familiarity with COUNTUNIQUEIFS syntax and basic formula editingRequired
Optional
- Optional: Google Sheets named ranges for readabilityOptional
- Optional: DATE and text/date criteria handling in formulasOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaAfter selecting the cell with the formula | Ctrl+C |
| Paste formulaInto the destination cell | Ctrl+V |
| Paste values onlyPaste without formatting | Ctrl+⇧+V |
| Undo last actionIf you make a mistake | Ctrl+Z |
FAQ
What is COUNTUNIQUEIFS in Google Sheets?
COUNTUNIQUEIFS counts the number of unique values in a range that meet multiple criteria. It combines COUNTUNIQUE with conditional filters, allowing multi-dimensional analysis without auxiliary pivot tables.
COUNTUNIQUEIFS gives you the count of distinct items that meet all your conditions, so you can compare segments quickly.
Does COUNTUNIQUEIFS count blanks or empty cells?
By default, blanks are not counted unless they appear in the count_range and meet the criteria. If you need to exclude blanks reliably, add a criterion that filters them out or use an alternative like FILTER+UNIQUE.
It usually ignores blanks unless your criteria accidentally include them.
Can I use DATE criteria with COUNTUNIQUEIFS?
Yes. Use date criteria like >= DATE(2026,1,1) and <= DATE(2026,12,31) or string dates in proper ISO formats. Ensure the date ranges align with the data type in the criterion_range.
Dates work with COUNTUNIQUEIFS as long as your ranges align and you format the criteria correctly.
How does COUNTUNIQUEIFS differ from COUNTIF or COUNTUNIQUE?
COUNTUNIQUEIFS counts unique values with multiple supported criteria. COUNTIF counts occurrences for a single condition, and COUNTUNIQUE returns unique counts for a single range without extra criteria.
It’s a specialized mix of uniqueness counting plus multi-criteria filtering.
What are common errors when using COUNTUNIQUEIFS?
Common errors include misaligned ranges, omitting required criteria, and using improper data types for criteria (e.g., text vs. numbers). Validate ranges and test with simple cases first.
Check your ranges line up and keep criteria types consistent to avoid errors.
The Essentials
- Countuniqueifs google sheets counts unique values meeting multiple criteria
- Syntax combines COUNTUNIQUE with multiple criterion ranges
- Ensure aligned ranges to avoid miscounts
- Use IFERROR to handle empty or invalid results
- Consider alternative FILTER+UNIQUE approach for large datasets