Unique Values
Get the distinct list first with =UNIQUE(A2:A500), then count or total against it using COUNTIF and SUMIF beside each value. UNIQUE spills one row per distinct value in the order they first appear, so =COUNTIF($A$2:$A$500, D2) filled down next to that spill gives the frequency of each, and =SUMIF($A$2:$A$500, D2, $B$2:$B$500) gives the amount per value — a summary table in two formulas and no PivotTable. Where only the number of distinct values is wanted, =COUNTA(UNIQUE(A2:A500)) is the entire answer. All of this needs Excel 365 or 2021; earlier versions produce the same list through Data > Advanced Filter with Unique records only, as a static copy that will not follow the data.
The formula
=UNIQUE(A2:A500) the distinct values, spilled in first-seen order
=COUNTA(UNIQUE(A2:A500)) how many distinct values there are
=UNIQUE(FILTER(A2:A500, A2:A500<>"")) the same list without the 0 that empty cells otherwise contribute
=UNIQUE(VSTACK(A2:A500, C2:C500)) distinct values pooled from two separate columns
=COUNTIF($A$2:$A$500, D2) how often the value in D2 occurs
=SUMIF($A$2:$A$500, D2, $B$2:$B$500) the total of column B for the value in D2
=UNIQUE(A2:A500, FALSE, TRUE) only values appearing exactly once — narrower than distinctA worked example
A2:A21 records which of five reps closed each of twenty deals; B2:B21 holds each deal value. Columns D, E and F are free.
=UNIQUE(A2:A21) in D2, then =COUNTIF($A$2:$A$21, D2) in E2 and =SUMIF($A$2:$A$21, D2, $B$2:$B$21) in F2, both filled down beside the spill
D spills the five names in the order they first appear in column A, not alphabetically; E gives each rep's deal count and F their total value. Enter a twenty-first deal in row 22 and nothing moves — all three ranges stop at row 21. Converting A:B to a Table with Ctrl+T and referencing the Table columns is what makes the summary keep up.
Which one do I need?
| If you want to… | Use |
|---|---|
| A live list of the distinct values in a column | =UNIQUE(range) — it spills, it updates itself, and it never touches the source |
| Just how many different values there are | =COUNTA(UNIQUE(range)) |
| Only the values that occur exactly once | =UNIQUE(range, FALSE, TRUE) — the third argument shifts the meaning from distinct to non-repeating |
| Distinct values pooled from two or more columns | =UNIQUE(VSTACK(A2:A500, C2:C500)) on Excel 365; older versions need the columns stacked into one range first |
| Excel 2019 or earlier, with no dynamic arrays | Data > Advanced Filter with Unique records only for a static list, or =SUMPRODUCT(1/COUNTIF(range,range)) for the count alone — it errors on any blank cell in the range |
| A count of distinct customers inside a PivotTable | Recreate the pivot with the Data Model ticked, then Value Field Settings > Distinct Count |
| The repeats should be deleted from the data itself | Data > Remove Duplicates edits the range in place; UNIQUE only ever reports |
| You want to know which values repeat, not which are distinct | A COUNTIF helper column marks them without moving anything |
Frequently asked questions
Why does UNIQUE return a #SPILL! error?
The cells the result needs are not free. Something occupies the range below or to the right, there are merged cells in the way, or the formula sits inside an Excel Table — dynamic arrays cannot spill within a Table. Clear the blocking cells, or move the formula outside the Table, and the spill appears.
Why is there a 0 in my list of unique values?
Empty cells inside the range come back as 0 rather than being skipped, so one blank row anywhere in A2:A500 adds a 0 to the result. Filter them out at source with =UNIQUE(FILTER(A2:A500, A2:A500<>"")), which is also what stops the list changing length as blanks come and go.
What is the difference between unique and distinct here?
By default UNIQUE returns distinct values: each value once, however many times it repeated. Setting the third argument to TRUE returns only the values that never repeated at all — a much shorter list. Asking for "unique customers" almost always means the first; asking for "customers who ordered only once" means the second.
Does the list update when I add new rows?
Only if the new rows fall inside the range you referenced. A formula written over A2:A500 ignores row 501 forever. Reference a Table column, or a whole column such as A:A wrapped in FILTER to drop the blanks, and the spill grows with the data.
New guides and tools, once a month
DE + EN · double opt-in · no spam