Counting occurences in a column

New Contributor

Hi ... I have a very long list of town names, more than 3,000. I would like to write a formula that would go down the column, and count the occurrences of each town name, if that's possible. I'm not talking about a COUNTIF that requires the criteria to be specified, I'm wondering if it's possible to automate that process. Thanks.

3 Replies


I'd create a pivot table based on the data list.

Add the Town field to both the Rows area and to the Values area.

See Create a Pivot Table in Excel if you're not familiar with pivot tables.

@Hans VogelaarPerfect! That did it, thanks!


Why so dismissive of COUNTIF?  The formula

= COUNTIFS(city, city)

cannot be that much of a problem.

Better, with 365, might be

= LET(
  distinct,     SORT(UNIQUE(city)),
  occurrences,  COUNTIFS(city,distinct),
  CHOOSE({1,2}, distinct, occurrences))

The entire set of result is generated by a single formula cell