# How to count distinct column values in Google Sheets?

I have a Google spreadsheet with a column that looks like this:

``````City
----
London
Paris
London
Berlin
Rome
Paris
``````

I want to count the appearances of each distinct city (so I need the city name and the number of appearances).

``````City   | Count
-------+------
London |  2
Paris  |  2
Berlin |  1
Rome   |  1
``````

How do I do that?

Solution

• 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.