count distinct values in spreadsheet

Question

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?

Deleted my answer due to I wasn't able to create a working example. I recommend having a look at a combination with `IF()` and `COUNTIF()` — sascha, Jan 25 '13 at 12:35

score 118 · Accepted Answer · edited Jan 20 '20 at 13:11

118

Link to Working Examples

Solution 0

This can be accompished using pivot tables.

Pivot table Example - Count rows by value

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

edited Jan 20 '20 at 13:11

Alan Franzoni

3,041
1
23
35

answered Jan 25 '13 at 14:09

JSuar

21,056
4
39
83

That worked. Thanks. Funny that I already applied the second solution with another set, but didn't think of it here. – Marius Bancila Jan 26 '13 at 14:06
for solution 2: you could leave column B empty and then use "count" instead of "sum": =sort(transpose(query(A3:B8,"Select count(B) pivot (A)")),2,FALSE) – wivku Dec 21 '14 at 11:36
...but how do I create a pivot table ? while this answer looks great and detailed, dnlbrky's more straightforward answer is the one which saved me. here, I didn't even notice the -more simple- alternative 1&2 solutions given. Too bad :( – Balmipour Mar 25 '16 at 01:22
1

@Balmipour Data > Pivot Table > then just copy the screenshot. – Joshua Dance Feb 23 '17 at 00:45
Just came back here after your comment. I didn't guess the french for "Pivot tables", was "tableaux croisés dynamiques". Guess I could have searched a little better that day, but at least, knowing the menu option was just in "Data" allowed me to find it. – Balmipour Feb 23 '17 at 18:46
Solution 2 is elegant and awesome! – mafonya Oct 28 '18 at 12:46
I used solution 1 but you have to use a semicolon and not a comma between parameters. =COUNTIF(A3:A8;B3) – P.O.W. Aug 27 '19 at 13:23

score 31 · Answer 2 · answered Jun 06 '17 at 23:47

31

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

answered Jun 06 '17 at 23:47

Mike Latch

311
3
2

This technique worked well and was easier to apply in my particular case. However, oddly the query seemed to break (i.e., it would not output anything) if there was more than one distinct numerical value in the column being counted. It worked when I converted the numerical values to their string equivalents though. – Matt V. Jul 04 '17 at 20:27
@MattV. I tried adding distinct numerical values into the column being counted and the query worked just fine. https://docs.google.com/spreadsheets/d/1N5xw8-YP2GEPYOaRkX8iRA6DoeRXI86OkfuYxwXUCbc/edit#gid=0&range=A1:D10 – Mike Latch Jul 12 '17 at 22:55
To label other columns, repeat their definition exactly. In this example: `label A 'City', count(A) 'Total'` – Bryan Roach Jan 11 '20 at 19:43

score 21 · Answer 3 · answered Sep 01 '13 at 20:03

21

=iferror(counta(unique(A1:A100))) counts number of unique cells from A1 to A100

answered Sep 01 '13 at 20:03

d-_-b

21,536
40
150
256

2

can you explain the function of iferror() in this case? counta(unique(A1:A100)) gave me the result I was looking for. – Stew Oct 31 '14 at 17:48
11

OP was not asking for a count of unique values but if you do want that then you could simplify it by using =COUNTUNIQUE(A:A) – wivku Dec 21 '14 at 10:30

Rudolf Real · Answer 4 · 2017-02-24T04:03:47.403

11

Not exactly what the user asked, but an easy way to just count unique values:

Google introduced a new function to count unique values in just one step, and you can use this as an input for other formulas:

=COUNTUNIQUE(A1:B10)

edited Feb 24 '17 at 04:03

answered Jun 25 '16 at 09:56

Rudolf Real

1,948
23
27

1

This will return just 1 number. The count of the unique values. However, the OP asked how to count each unique value. – Joshua Dance Feb 23 '17 at 00:34
3

@JoshuaDance You're right, but the op's post is called "count distinct values in spreadsheet". This looks like a legit answer to this precise question to me. Just note that it doesn't count "empty cell" as a value – Balmipour Feb 23 '17 at 18:37
1

If you guys are fine with, I will leave it for now. Unless too many users find this a bad answer so I will delete it. @Balmipour – Rudolf Real Feb 24 '17 at 04:02
1

This is exactly what I needed. – FellyTone84 Apr 19 '17 at 16:41
If you came from Google, this is the correct answer – Matt Jan 25 '21 at 19:01

score 7 · Answer 5 · edited Apr 10 '19 at 03:55

7

This works if you just want the count of unique values in e.g. the following range

=counta(unique(B4:B21))

edited Apr 10 '19 at 03:55

xskxzr

12,442
12
37
77

answered Apr 10 '19 at 03:35

Paul

71
1
1

score 1 · Answer 6 · answered May 06 '23 at 19:21

1

Now with the recent release of the LAMBDA function this can be done even more elegantly..

={UNIQUE(A2:A7),MAP(UNIQUE(A2:A7),LAMBDA(city,COUNTIF(A2:A7,city)))}

answered May 06 '23 at 19:21

Fabian

443
4
12

score 0 · Answer 7 · answered Oct 13 '14 at 19:33

This is similar to Solution 1 from @JSuar...

Assume your original city data is a named range called dataCity. In a new sheet, enter the following:

    A                 | B
  ----------------------------------------------------------
1 | =UNIQUE(dataCity) | Count
2 |                   | =DCOUNTA(dataCity,"City",{"City";$A2})
3 |                   | [copy down the formula above]
4 |                   | ...
5 |                   | ...

score -4 · Answer 8 · edited Oct 16 '22 at 17:24

-4

=UNIQUE({filter(Core!L8:L27,isblank(Core!L8:L27)=false),query(ArrayFormula(countif(Core!L8:L27,Core!L8:L27)),"select Col1 where Col1 <> 0")})

Where Core!L8:L27 is the list in the question.

edited Oct 16 '22 at 17:24

TheMaster

45,448
6
62
85

answered Nov 15 '18 at 08:10

user3626588

21
1

count distinct values in spreadsheet

8 Answers8

Linked