Want to highlight a helpful answer? Upvote!
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
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
