Quick Answer & Formula
Excel
Sheets
Beginner
Modern: =COUNTA(UNIQUE(A2:A100)) | Classic: =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) In modern Excel and Google Sheets: =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))). This extracts the distinct values, ignores empty rows, and counts the result.
How to Count Unique and Distinct Values in Excel & Google Sheets
Counting unique entries (e.g. how many distinct customers purchased this month) is standard in analytics dashboards.
1. Modern Formula (Excel 365 & Google Sheets)
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100 <> "")))FILTER(..., <> ""): Strips out empty cells so blanks aren’t counted as a unique item.UNIQUE(...): Extracts distinct entries.COUNTA(...): Counts the total number of distinct entries.
2. Classic Universal Formula (All Excel Versions)
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100 & ""))
?
Frequently Asked Questions
How do I count unique values that meet a specific condition (e.g. Unique customers from California)?
Combine COUNTA, UNIQUE, and FILTER: =COUNTA(UNIQUE(FILTER(Customer_Col, State_Col = "CA"))).