Thread: Counting a List of Strings

If I have a list of cities:

Atlanta
San Diego
Detroit
Atlanta
New York
Kansas City
New York
Atlanta
Detorit
Atlanta
...
Is there a way for Excel to show me how many times a city appears in a list? The list is long and contains a lot of cities so I don't think I can say IF="Atlanta" Think of 100 different cities and many I have never heard of before.
The result should look similar to this:

Atlanta 4
San Diego 1
Detroit 2
New York 2
Kansas City 1

Thanks for anyone's help on this

bhova

Use Pivot Table to get the unique value, then use countif

Wow, that is exactly what I want.
How to I find Pivot Strings? Ha

Thanks,

bhova

What do you mean?

If you just want the unique values, highlight column A, go to Insert > Pivot Table > Drag cities to row labels field.

I've never used that function. I see that it is more complicated then just typing in a formula. But I think I figured it out. thanks so much for your help.

Much appreciated. You made my day a whole lot better

bhova

Hi,
Why don't you use all the capabilities of pivot table to get the result ?
See attached file
Best regards