Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google sheets - count distinct values in spreadsheet

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...