# How to count how many times each value appears in Excel

In Excel 365, list each value once and count it in one formula: `=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))`.

**Excel:** `=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12))))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Size |  |  | Size | Orders |
| M |  |  |  |  |
| L |  |  |  |  |
| S |  |  |  |  |
| M |  |  |  |  |
| XL |  |  |  |  |
| M |  |  |  |  |
| L |  |  |  |  |
| S |  |  |  |  |
| M |  |  |  |  |
| L |  |  |  |  |
| M |  |  |  |  |

Formula in D2: `=HSTACK(UNIQUE(A2:A12),COUNTIF(A2:A12,UNIQUE(A2:A12)))`

Result (spills 4 × 2): M, 5; L, 3; S, 2; XL, 1

## 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.

## Other ways

- `=LET(u,UNIQUE(A2:A12),HSTACK(u,COUNTIF(A2:A12,u)))`: The same table with LET, so UNIQUE runs once.
- `=QUERY(A1:A12,"select A, count(A) group by A",1)`: 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<>""))`.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-how-many-times-each-value-appears