countgoogle-sheetsdistinct# 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

**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**

- PySpark: count over a window with reset
- XPath1 at SQL/XML, count elements returns empty
- C# Count even numbers from console imputed string
- Write a program to return the length of the longest word from a string whose length is even?
- Counting Records in Linux Files Excluding Some Files
- SPLUNK: How can I count events by location with a list of SERVERNAME's?
- Count # of checkboxes as part of a function within Google Apps Script?
- SQL, Order with multiple rows showing same Total value, how to count once?
- Run a query with a LIMIT/OFFSET and also get the total number of rows
- SQL Count Distinct Colums and Return all rows
- How to calculate the number of values of current selection which has repeated in older entries
- How to count the row count matching the selected data set, where the issue type match
- Python : count function does not work
- How to count the values to prepare make stacked bar chart
- MYSQL get count of each column where it equals a specific value
- print column name when specific row of a column contains string in R
- How to summarise by group and total with missing counts using dplyr?
- getting the total count of search results in wordpress
- Unexpected result with list.count(True) in Python
- Including count = zero results in SQL count query
- One SQL count query
- Compress EACH LINE of a file individually and independently of one another? (or preserve newlines)
- How to query for the number of distinct tuples using SPARQL 1.1?
- Sort a huge collection of text files
- Count occurrences of a variable having two given values corresponding to one value of another variable
- How can I count only "TRUE" as a result of count function, and conver 0 for "FALSE" output?
- Returning row # in google sheets
- From two data frames, is there any way to count unique values of a column in R?
- Add customer order count to WooCommerce admin orders list (HPOS)
- In Python, count the number of variables in a class or prevent adding new class variables