Count Unique Values in Excel: Distinct vs Exactly Once
“Unique” can mean each different value once or only values that occur exactly once. This workbook calculates both from the same eight-cell list.
Work through the eight cells
In A4:A11 the example is North, South, North, blank, West, South, East, blank. There are four distinct nonblank values: North, South, West, East. Only two occur exactly once: West and East. COUNTBLANK returns two. These are different answers to different questions.
| Output | Cell | Expected |
|---|---|---|
| Distinct nonblank | B14 | 4 |
| Exactly once | B15 | 2 |
| Blanks | B16 | 2 |
Use the helper method on older Excel
Column B counts each nonblank value with COUNTIF($A$4:$A$11,A4). Column C marks the first occurrence with =IF(A4="",0,IF(COUNTIF($A$4:A4,A4)=1,1,0)). Column D marks values whose total frequency is one. Sum C for distinct nonblank values; sum D for exactly-once values. The formulas are already filled through row 11 in the workbook. Extend all bounded ranges when your input grows. Do not infer blank count from COUNTA when formula-empty cells are in the input.
Modern dynamic-array alternative
On Microsoft 365, Excel 2021/2024 and supported Mac versions, =ROWS(UNIQUE(FILTER(A4:A11,A4:A11<>""))) counts distinct nonblank values. Add FALSE,TRUE to UNIQUE for exactly-once values. Microsoft defines that third argument as exactly_once. The modern examples are in B18:B19. Leave spill cells clear when returning the lists instead of counts. UNIQUE is not available in older perpetual versions such as Excel 2016/2019; use the helper method there. Formula argument separators follow your Excel locale.
Sources: Microsoft Support: UNIQUE and Microsoft Support: ways to count values.