count distinct values in spreadsheet
Link to Working Examples
Solution 0
This can be accompished using pivot tables.
Solution 1
Use the unique
formula to get all the distinct values. Then use countif
to get the count of each value. See the working example link at the top to see exactly how this is implemented.
Unique Values Count
=UNIQUE(A3:A8) =COUNTIF(A3:A8;B3)
=COUNTIF(A3:A8;B4)
...
Solution 2
If you setup your data as such:
City
----
London 1
Paris 1
London 1
Berlin 1
Rome 1
Paris 1
Then the following will produce the desired result.
=sort(transpose(query(A3:B8,"Select sum(B) pivot (A)")),2,FALSE)
I'm sure there is a way to get rid of the second column since all values will be 1. Not an ideal solution in my opinion.
via http://googledocsforlife.blogspot.com/2011/12/counting-unique-values-of-data-set.html
Other Possibly Helpful Links
- http://productforums.google.com/forum/#!topic/docs/a5qFC4pFZJ8
You can use the query
function, so if your data were in col A where the first row was the column title...
=query(A2:A,"select A, count(A) where A != '' group by A order by count(A) desc label A 'City'", 0)
yields
City count
London 2
Paris 2
Berlin 1
Rome 1
Link to working Google Sheet.
https://docs.google.com/spreadsheets/d/1N5xw8-YP2GEPYOaRkX8iRA6DoeRXI86OkfuYxwXUCbc/edit#gid=0