Apple’s Worldwide Developers Conference to kick off June 10 at 10 a.m. PDT with Keynote address
The Keynote will be available to stream on apple.com, the Apple Developer app, the Apple TV app, and the Apple YouTube channel. On-demand playback will be available after the conclusion of the stream.
The Keynote will be available to stream on apple.com, the Apple Developer app, the Apple TV app, and the Apple YouTube channel. On-demand playback will be available after the conclusion of the stream.
I have a column of city names. I want to create a list of unique city names and a count of that city name.
For example:
| City Name |
|---|
Los Angeles |
| New York City |
| Los Angeles |
| Los Angeles |
| Seattle |
New York City |
| Miami |
| Dallas |
| Seattle |
| Dallas |
| Dallas |
I would like a count of each unique city name.
For example:
| City Name | Count |
|---|---|
| Los Angeles | 3 |
| New York City | 2 |
| Seattle | 2 |
| Miami | 1 |
| Dallas | 3 |
Ideas?
Thanks.
Numbers-OTHER
Aaron,
You can use the COUNTIF() function like this:
This assumes the table on the left is titled "Table 1". If it is not change the table name or the name used in the formula from "Table 1" to what ever the name you used.
In the summary table (on the right):
B2=COUNTIF(Table 1 :: A, A2)
select B2 and fill down as needed
Aaron,
The following solution finds distinct city names, lists them and counts the frequency of ocurrance.
The formula in column B of Data is:
=IF(COUNTIF(OFFSET($A$1, 0,0, ROW()-1),A)=0, MAX(OFFSET($A$1, 0, 1, ROW()-1))+1, "")
The formula in column A of Summary is:
=IF(MAX(Data :: B)>=ROW()-1, LOOKUP(ROW()-1, Data :: B, Data :: A), "")
And, the formula in column B of Summary is:
=COUNTIF(Data :: A, A)
if you are in fact interested in the frequency.
Hope this does it for you.
You asked for unique names, but I believe that you mean to say distinct. The only unique name in your list is Miami, since it's the only one that appears only once, making it unique.
Jerry
Count unique values in a column
