How to count how many times each value appears in Excel
Excel formula
=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))In Excel 365, list each value once and count it in one formula: =HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12))).
- Result on the example
- a 4-row list that spills from D2 in
D2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE returns each size once, in the order they first appear. Given that list as its criteria, COUNTIF returns one count per size. HSTACK puts the sizes and their counts side by side, and the result spills into a two-column table that grows when new sizes appear.
In Google Sheets, wrap the formula in ARRAYFORMULA so COUNTIF counts every size in the list.
=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))Result{4 rows × 2 cols} "M", 5, "L", 3, …
A2:A1211 rows × 1 col: "M", "L", "S", "M", …A range: 11 cells in one column.
UNIQUE(A2:A12){4 rows × 1 col} "M", "L", "S", "XL"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1211 rows × 1 col: "M", "L", "S", "M", …
- array
UNIQUE(A2:A12){4 rows × 1 col} "M", "L", "S", "XL"UNIQUE: Distinct rows of a range. Spills.- array
A2:A1211 rows × 1 col: "M", "L", "S", "M", …
- array
COUNTIF(A2:A12,UNIQUE(A2:A12)){4 rows × 1 col} 5, 3, 2, 1COUNTIF: Counts cells that meet one condition.- range
A2:A1211 rows × 1 col: "M", "L", "S", "M", … - criteria
UNIQUE(A2:A12){4 rows × 1 col} "M", "L", "S", "XL"
- range
HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12))){4 rows × 2 cols} "M", 5, "L", 3, …HSTACK: Places ranges side by side.- array1
UNIQUE(A2:A12){4 rows × 1 col} "M", "L", "S", "XL" - array2
COUNTIF(A2:A12,UNIQUE(A2:A12)){4 rows × 1 col} 5, 3, 2, 1
- array1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LET(u,UNIQUE(A2:A12),HSTACK(u,COUNTIF(A2:A12,u))) | spills 4 × 2 | The same table with LET, so UNIQUE runs once. |
| =QUERY(A1:A12,"select A, count(A) group by A",1)Google Sheets | spills 5 × 2Different result | Google Sheets only: a frequency table with headers, sorted by size. Adds a header row and sorts the sizes alphabetically. |
Common mistakes
- If anything is typed in the cells the table needs, the formula shows #SPILL!. Clear the area to the right and below.
- In Excel 2019 and earlier, type the values in a column yourself and fill
=COUNTIF($A$2:$A$12,D2)down next to them. - Blank cells in the range show up as a 0 row in UNIQUE. Filter them out first:
UNIQUE(FILTER(A2:A20,A2:A20<>"")).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count how many times each value appears
D2, list each size once with the number of times it appears in A2:A12.Questions
How do I sort the result by count?
Wrap it in SORT on the second column, descending: =SORT(HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12))),2,-1).
Can a pivot table do this?
Yes. Put the column in Rows and again in Values, where it defaults to Count.
How do I show percentages instead of counts?
Divide by the total: =COUNTIF(A2:A12,UNIQUE(A2:A12))/COUNTA(A2:A12), formatted as a percentage.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.