Build a distinct list and how often each item appears — the formula version of a quick frequency table. UNIQUE spills the distinct values; COUNTIF tallies each one.
The example
Distinct regions and how many times each occurs.
| A | B | |
|---|---|---|
| 1 | Region | Count |
| 2 | East | 3 |
| 3 | West | 2 |
The formula
Distinct values beside their counts:
How it works
Two spilling functions, combined:
UNIQUE(range)spills the distinct values.COUNTIF(range, UNIQUE(range))counts each distinct value against the full list — it spills a matching column of counts.HSTACKplaces the values and counts side by side as a two-column table.- Sort it by count with
SORTBY(…, counts, -1)to rank the most common first.
Or just use GROUPBY: =GROUPBY(Region, Region, COUNTA) produces the same distinct-with-count table in 2024 Excel. The UNIQUE+COUNTIF combo works in any 365/2021 build and is easy to sort or filter.
Try it: interactive demo
Items (one per line) → distinct + counts.
Variations
Sort by count
Most common first:
GROUPBY (2024)
One function:
Distinct count only
How many uniques:
Pitfalls & errors
HSTACK is 365. If unavailable, put UNIQUE in one column and COUNTIF beside it manually.
Blanks become an item. UNIQUE includes a blank as a distinct value; filter it out if unwanted.
Spill room. Both functions spill — keep neighboring cells clear.
Practice workbook
Frequently asked questions
How do I make a unique list with counts in Excel?
How do I sort the list by frequency?
Is there a one-function version?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class