Mar 16 2021 08:56 AM
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.
Mar 16 2021 09:23 AM
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.
Mar 16 2021 09:29 AM
@Hans VogelaarPerfect! That did it, thanks!
Mar 16 2021 01:00 PM
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