Forum Discussion
Counting occurences in a column
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
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
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.
HansVogelaarPerfect! That did it, thanks!
