Count Unique Values: Methods in Excel, Python, SQL
This article explains what “count unique values” means, why it matters, and how to do it in the most common tools: Excel, Google Sheets, Python, and SQL. You’ll get clear formulas, code snippets, and the key differences between counting unique and distinct values.
What Does “Count Unique” Mean?
“Count unique values” usually means counting the number of distinct entries in a list, ignoring duplicates. For example, in the list A, B, A, C, the unique values are A, B, C — so the count is 3. However, some tools also offer a “count unique” option that includes only values appearing exactly once. In that same list, only B and C appear once, so that count would be 2. Always check which definition your tool uses.
Why Counting Unique Values Matters
You need this operation in many real situations:
- Number of unique customers who made a purchase (not total transactions).
- Number of distinct products sold in a month.
- Number of unique IP addresses visiting a website.
- Number of different error codes in a log file.
- Number of unique responses in a survey (excluding repeat entries).
Getting this wrong leads to inflated numbers and poor decisions, so it’s worth doing carefully.
Excel and Google Sheets
In Excel 365 and Google Sheets, the simplest way is COUNTA(UNIQUE(range)). For example, =COUNTA(UNIQUE(A2:A100)) counts the number of distinct text or number entries in that range. If your data might contain empty cells, use =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))) to exclude blanks.
Older Excel versions use the array formula =SUM(IF(COUNTIF(range, range)=1, 1, 0)) entered with Ctrl+Shift+Enter. That counts values that appear exactly once, not all distinct values. For distinct counts in older Excel, you can use =SUMPRODUCT(1/COUNTIF(range, range)), but this fails if there are blank cells or if the range is large.
In Google Sheets, you can also use =COUNTUNIQUE(A2:A100), which counts distinct values ignoring blanks. For values appearing exactly once, use =COUNTA(UNIQUE(A2:A100)) - SUMPRODUCT((COUNTIF(A2:A100, UNIQUE(A2:A100))>1)*1) — but the simpler approach is a filter.
Python (Pandas)
In Python with pandas, the nunique() method gives the number of distinct values. For a Series:
import pandas as pd
df = pd.DataFrame({'item': ['apple', 'banana', 'apple', 'cherry']})
print(df['item'].nunique()) # Output: 3
If you want to count values that appear exactly once, you can use value_counts() and filter:
vc = df['item'].value_counts()
print((vc == 1).sum()) # Output: 2 (banana and cherry)
For a DataFrame, df.nunique() returns the count of distinct values for each column. To get the distinct rows, use df.drop_duplicates().shape[0].
SQL
In SQL, the standard is COUNT(DISTINCT column). For example:
SELECT COUNT(DISTINCT customer_id) FROM orders;
This counts the number of distinct customer IDs in the orders table. If you need to count values that appear exactly once, you can use a subquery:
SELECT COUNT(*) FROM (
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1
) AS single_occurrence;
Note that COUNT(DISTINCT) ignores NULLs. If you need to include NULL as a value, you might need to handle it separately, depending on your database.
Common Mistakes and Pitfalls
- Confusing “unique” with “distinct”: Distinct means all different values; unique often means appearing only once. Always read the documentation.
- Ignoring blanks: Some functions count blanks as a value, others ignore them. In Excel,
COUNTAignores blanks, butUNIQUEincludes an empty string if present. In SQL,COUNT(DISTINCT)ignores NULLs. - Case sensitivity: Excel and pandas treat “Apple” and “apple” as different. SQL depends on the collation. If you need case-insensitive counting, convert to lowercase first.
- Whitespace: “apple ” and “apple” are different values. Trim data before counting.
- Large datasets: In Excel,
SUMPRODUCT(1/COUNTIF(...))can be slow and may crash on large ranges. Use pivot tables or Power Query for big data.
How to Choose the Right Method
Your choice depends on your tool and your definition:
- If you need distinct count in Excel 365 or Google Sheets, use
COUNTA(UNIQUE(...)). - If you need unique (appearing once) count, use a method that counts frequencies and then counts how many have frequency 1.
- In Python, use
nunique()for distinct, andvalue_counts()for frequency-based unique. - In SQL, use
COUNT(DISTINCT ...)for distinct, and a subquery withHAVING COUNT(*)=1for unique.
Frequently Asked Questions
What is the difference between unique and distinct values?
Distinct values are all different values in a list, regardless of how many times they appear. Unique values are those that appear exactly once. For example, in [A, B, A, C], distinct values are A, B, C (count 3), while unique values are B and C (count 2).
How do I count unique values in Excel with multiple columns?
To count distinct combinations across multiple columns, you can create a helper column that concatenates the values, then count unique of that helper. In Excel 365, you can use =COUNTA(UNIQUE(A2:A100&"-"&B2:B100)) but be careful with delimiter collisions. Alternatively, use a pivot table or Power Query.
Does COUNT(DISTINCT) in SQL count NULLs?
No, COUNT(DISTINCT column) ignores NULL values. If you need to count NULL as a distinct value, you can use COUNT(DISTINCT COALESCE(column, 'NULL_SENTINEL')) but that changes the result if a real value equals the sentinel.
Why does my unique count in Python include NaN?
By default, pandas nunique() excludes NaN values. If you want to include NaN as a distinct value, use df['col'].nunique(dropna=False). Similarly, value_counts(dropna=False) shows NaN as a category.
Can I count unique values in a pivot table?
Yes. In Excel, add your data to a pivot table, then drag the field to the Values area and change the value field settings to “Distinct Count”. In Google Sheets, you can use a pivot table with “COUNTUNIQUE” as the summarise by option. In SQL, you can use a GROUP BY with COUNT(DISTINCT ...).
What is the fastest way to count unique values in a very large dataset?
For large data, use a database or a tool like Python with pandas, which is optimized. In Excel, Power Query is better than array formulas. For huge datasets, consider using approximate algorithms like HyperLogLog if a small margin of error is acceptable.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.