Count Each Item in a List in Excel (4 Ways)

by

To see how many times each item appears, use UNIQUE and COUNTIF in Excel 365, GROUPBY for a sorted summary, a PivotTable, or Power Query’s Group By. All four update when the list changes.

Excel 365UNIQUE + COUNTIF, or GROUPBY
Any versionPivotTable
RefreshablePower Query Group By

The list

The list is an Excel Table named ABCD with one column, Items.

List of items in an Excel Table

1. UNIQUE and COUNTIF (Excel 365)

=HSTACK(UNIQUE(ABCD[Items]),COUNTIF(ABCD[Items],UNIQUE(ABCD[Items])))

UNIQUE lists each item once; COUNTIF counts each one. HSTACK puts them in two columns side by side.

UNIQUE returning the distinct items
COUNTIF counting each unique item

For one column of text such as Apple – 4: =UNIQUE(ABCD[Items])&" - "&COUNTIF(ABCD[Items],UNIQUE(ABCD[Items])).

Item names joined with their counts

2. GROUPBY (current Excel 365)

=GROUPBY(ABCD[Items],ABCD[Items],COUNTA,0,0,-2)

Returns each item with its count, sorted from most to least. See GROUPBY.

3. PivotTable

Select the table → Table Design → Summarize with PivotTable → OK. Drag Items to Rows and again to Values.

Summarize with PivotTable
Items in the Rows area
Count of each item in the PivotTable

Right-click → Refresh after the list changes.

4. Power Query

Data → From Table/Range → Home → Group By → Count Rows → OK → Close & Load To.

Group By in Power Query
Group By dialog with Count Rows
Counts loaded into Excel

All four updating

All four methods updating after items are added

Formulas update instantly; the PivotTable and Power Query update when you click Refresh.

Related