Count distinct values with COUNTA and UNIQUE

How it works, step by step

1. 01FILTER(B2:B5000,B2:B5000<>"") removes empty cells, so the unused rows at the bottom of the range are not counted as a value.
2. 02UNIQUE returns each email once, in the order it first appears. 4,812 order rows might collapse to 1,309 emails.
3. 03COUNTA counts the items in that list and returns 1,309.
4. 04In Google Sheets, =COUNTUNIQUE(B2:B5000) gives the same count in one function and skips blanks on its own.

The same logic in four tools

=COUNTA(UNIQUE(FILTER(B2:B5000,B2:B5000<>"")))

=COUNTUNIQUE(B2:B5000)

SELECT COUNT(DISTINCT customer_email) AS customers
FROM orders;

import pandas as pd

customers = orders["customer_email"].nunique()

Mistakes that break it

• !Excel UNIQUE ignores case, so Ana@shop.com and ana@shop.com count once. Google Sheets UNIQUE, SQL COUNT(DISTINCT), and pandas nunique() are case-sensitive and count them twice. Normalize with LOWER or .str.lower() first.
• !A trailing space makes two emails look identical on screen but count separately. Add TRIM inside: =COUNTA(UNIQUE(TRIM(FILTER(...)))).
• !Without the FILTER step, a range with empty cells adds one extra item for the blank, so COUNTA(UNIQUE(B2:B5000)) comes out one too high.
• !UNIQUE and FILTER need Excel 365 or Excel 2021. In older Excel, =SUMPRODUCT((B2:B5000<>"")/COUNTIF(B2:B5000,B2:B5000&"")) returns the same count but slows down past a few thousand rows.

Common questions

How do I count unique values in Google Sheets?

Use =COUNTUNIQUE(B2:B5000). It counts each distinct value once and ignores empty cells.

What is the SQL equivalent of COUNTA UNIQUE?

COUNT(DISTINCT column). It ignores NULLs, but an empty string counts as a value, so add WHERE column <> '' if your data has empty strings.

How do I count distinct values per group?

In SQL, use COUNT(DISTINCT customer_email) with GROUP BY on the grouping column, for example region. In pandas, use orders.groupby("region")["customer_email"].nunique().