How to count distinct column values in Google Sheets?

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
– sascha, Commented Jan 25, 2013 at 12:35

Alan Franzoni · Accepted Answer · 2020-01-20 13:11:20Z

119

+50

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, 2020 at 13:11

Alan Franzoni

3,2091 gold badge26 silver badges36 bronze badges

answered Jan 25, 2013 at 14:09

JSuar

21.1k4 gold badges43 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wivku Over a year ago

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)

Balmipour Over a year ago

...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 :(

Joshua Dance Over a year ago

@Balmipour Data > Pivot Table > then just copy the screenshot.

Balmipour Over a year ago

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.

P.O.W. Over a year ago

I used solution 1 but you have to use a semicolon and not a comma between parameters. =COUNTIF(A3:A8;B3)

Mike Latch · Accepted Answer · 2017-06-06 23:47:45Z

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 6, 2017 at 23:47

Mike Latch

3113 silver badges2 bronze badges

3 Comments

Matt V. Over a year ago

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.

Mike Latch Over a year ago

@MattV. I tried adding distinct numerical values into the column being counted and the query worked just fine. docs.google.com/spreadsheets/d/…

Bryan Roach Over a year ago

To label other columns, repeat their definition exactly. In this example: label A 'City', count(A) 'Total'

d-_-b · Accepted Answer · 2013-09-01 20:03:08Z

21

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

answered Sep 1, 2013 at 20:03

d-_-b

23.2k43 gold badges174 silver badges291 bronze badges

2 Comments

Stew Over a year ago

can you explain the function of iferror() in this case? counta(unique(A1:A100)) gave me the result I was looking for.

wivku Over a year ago

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)

Rudolf Real · Accepted Answer · 2017-02-24 04:03:47Z

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, 2017 at 4:03

answered Jun 25, 2016 at 9:56

Rudolf Real

2,03825 silver badges27 bronze badges

5 Comments

Joshua Dance Over a year ago

This will return just 1 number. The count of the unique values. However, the OP asked how to count each unique value.

Balmipour Over a year ago

@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

Rudolf Real Over a year ago

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

FellyTone84 Over a year ago

This is exactly what I needed.

Matt Over a year ago

If you came from Google, this is the correct answer

xskxzr · Accepted Answer · 2019-04-10 03:55:42Z

6

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

=counta(unique(B4:B21))

edited Apr 10, 2019 at 3:55

xskxzr

13.1k13 gold badges43 silver badges86 bronze badges

answered Apr 10, 2019 at 3:35

Paul

691 silver badge1 bronze badge

Comments

Fabian · Accepted Answer · 2025-01-17 21:37:32Z

1

This works great!

=LET(cities,A1:A6,{UNIQUE(cities),MAP(UNIQUE(cities),LAMBDA(city,COUNTIF(cities,city)))})

Edit range if needed from A1:A6 to anything you like.

edited Jan 17 at 21:37

answered May 6, 2023 at 19:21

Fabian

4601 gold badge5 silver badges16 bronze badges

Comments

dnlbrky · Accepted Answer · 2014-10-13 19:33:36Z

0

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

answered Oct 13, 2014 at 19:33

dnlbrky

9,8852 gold badges55 silver badges65 bronze badges

Comments

TheMaster · Accepted Answer · 2022-10-16 17:24:03Z

-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, 2022 at 17:24

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

answered Nov 15, 2018 at 8:10

user3626588

211 bronze badge

Collectives™ on Stack Overflow

How to count distinct column values in Google Sheets?

8 Answers 8

5 Comments

3 Comments

2 Comments

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

3 Comments

2 Comments

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related