Formula guide · Excel and Google Sheets · Updated May 13, 2026
Count Unique Values in Excel or Google Sheets
Count distinct names, IDs, emails, or categories in a spreadsheet range.
Quick Answer
Use UNIQUE to return distinct values, then wrap it with COUNTA to count them.
Copyable Formula
=COUNTA(UNIQUE(B2:B100))Syntax
=COUNTA(UNIQUE(range))Excel and Google Sheets
Worked Example
| B |
|---|
| Region |
| West |
| East |
| West |
| North |
Result: Returns 3 because West, East, and North are the distinct values.
Steps
- Select the range to de-duplicate.
- Use UNIQUE to return one copy of each value.
- Wrap the result in COUNTA.
- Clean spaces first if repeated values may not be identical.
Common Mistakes
- Counting blanks as unique values when the source range contains empty cells.
- Ignoring hidden spaces or inconsistent capitalization.
- Using COUNT instead of COUNTA for text lists.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.