Count Unique Values in Google Sheets: Practical Guide
Learn how to count unique values in Google Sheets using COUNTUNIQUE, UNIQUE + COUNTA, and multi-range formulas. This step-by-step guide provides practical examples, tips, and troubleshooting to ensure accurate results.
Want to count unique values in Google Sheets? This guide covers quick methods using COUNTUNIQUE, and the combination of UNIQUE with COUNTA for blanks. You’ll learn to count unique items within a single range or across multiple ranges with clear examples, caveats about blanks, and tips for reliable results every time.
What counts as a unique value in Google Sheets
According to How To Sheets, counting unique values means identifying items with no exact duplicates in the chosen range. The How To Sheets team emphasizes that a true unique value is determined by exact content, not by how it appears in the cell. A few practical nuances matter:
- Text values: case and leading/trailing spaces can make two visually similar entries distinct unless normalized. Consider applying LOWER/UPPER and TRIM to standardize.
- Numbers and dates: numeric values and date serials are counted by their actual numeric value. Dates are numbers under the hood; formatting does not change their identity for counting.
- Blanks: blank cells are typically ignored by COUNTUNIQUE, but if you need to treat blanks as a value, convert them to a placeholder string like "<BLANK>" before counting.
Quick methods to count unique values
There are two reliable paths to count unique values in Google Sheets: a direct function and a composition of functions. Direct methods are concise, while composed methods offer control over blanks, case, and multi-range data. Both approaches work well for single-column data and larger datasets.
Method A: COUNTUNIQUE
COUNTUNIQUE(range) is the simplest way to count distinct values in a single range. For example, =COUNTUNIQUE(A2:A100) returns the number of unique entries in that column. If you need to ignore blanks, wrap the range in FILTER to exclude empty cells: =COUNTUNIQUE(FILTER(A2:A100, A2:A100<>'')). For large datasets, be mindful of full-column references like A:A, which can slow recalculation.
Method B: UNIQUE + COUNTA
UNIQUE returns a list of distinct values from a range. COUNTA then counts how many items are in that list: =COUNTA(UNIQUE(A2:A100)). To ignore blanks, exclude them from the unique list: =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>''))). This approach is particularly useful when you also want to retrieve the actual distinct values for other analysis.
Handling blanks, errors, and duplicates
To count unique values accurately, you often need to manage blanks and errors separately. Techniques include: using FILTER to remove blanks, using IFERROR to handle non-numeric entries, and applying TRIM to remove stray spaces. If you’re counting across multiple ranges, consider array literals like {A2:A100; B2:B100} to combine inputs.
Counting unique values across multiple ranges
You can count unique values across multiple ranges by combining them with an array literal. Example: =COUNTUNIQUE({A2:A100; B2:B100; C2:C100}). You can also use COUNTA with UNIQUE on the combined array: =COUNTA(UNIQUE({A2:A100; B2:B100})) to obtain both the list and its count.
Practical examples
Consider a class roster with student IDs in column A and enrollment codes in column B. You can count how many unique IDs you have, or how many unique enrollment codes exist across both columns. For survey data, counting unique responses helps you estimate the breadth of responses without counting duplicates.
Tools & Materials
- Google account(Needed to access Google Sheets)
- Google Sheets(Web or mobile app)
- Sample dataset(Data range to practice on (e.g., A2:A100))
- Helper columns (optional)(For intermediate calculations)
Steps
Estimated time: 15-25 minutes
- 1
Prepare your data
Ensure your data is in a single column or clearly defined range. Remove stray spaces and standardize formats if possible before counting.
Tip: Use TRIM and CLEAN to normalize text before counting. - 2
Choose your counting method
Decide whether you want a direct count (COUNTUNIQUE) or a two-step approach (UNIQUE + COUNTA) for additional flexibility.
Tip: If you plan to ignore blanks, test with FILTER to verify results. - 3
Enter the formula for a single range
Type =COUNTUNIQUE(A2:A100) or =COUNTA(UNIQUE(A2:A100)) in an empty cell to obtain the count of unique values in column A.
Tip: Double-check the range to avoid missing data or computing extra rows. - 4
Handle blanks and non-numeric data
If blanks or text entries skew your count, wrap the range with FILTER to exclude blanks or apply VALUE to coerce numbers.
Tip: Use FILTER(A2:A100, A2:A100<>'' ) to ignore blanks. - 5
Count across multiple ranges
Use array literals to combine ranges, e.g., =COUNTUNIQUE({A2:A100; B2:B100}).
Tip: Separate ranges with a semicolon to stack them vertically in the same count. - 6
Validate results
Cross-check by listing the unique values with =UNIQUE(A2:A100) and counting them, or by using pivot tables for a quick check.
Tip: Spot-check a sample of items to ensure accuracy.
FAQ
What is a unique value in Google Sheets?
A unique value is a value that appears only once in the considered range. In practice, duplicates are collapsed by functions like COUNTUNIQUE or UNIQUE.
A unique value is one that appears only once in your data.
How does COUNTUNIQUE handle blanks?
COUNTUNIQUE generally ignores blank cells, but you can explicitly exclude blanks with FILTER to ensure blanks aren’t counted as a value.
Blanks are usually ignored unless you specifically include them.
When should I use COUNTUNIQUE vs UNIQUE + COUNTA?
COUNTUNIQUE is faster for a simple count. If you also need the list of unique values, use UNIQUE and COUNTA to count them and display them.
Use COUNTUNIQUE for quick counts or UNIQUE with COUNTA when you need the list as well.
Can I count unique values across multiple ranges?
Yes. Use an array literal like =COUNTUNIQUE({A2:A100; B2:B100}) to combine ranges into a single unique count.
Yes, combine ranges with an array to count uniques.
How can I verify the results?
Cross-check by listing the unique values with =UNIQUE(A2:A100) or by using a pivot table to summarize distinct entries.
List the uniques with UNIQUE or check with a pivot.
Watch Video
The Essentials
- Count unique values with COUNTUNIQUE for simple ranges
- Use UNIQUE + COUNTA to control blanks
- Exclude blanks with FILTER for accurate counts
- Combine multiple ranges with array literals
- Normalize data before counting to reduce errors
