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

User · Accepted Answer

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

User · Answer

iferror counta unique A1 A100    counts number of unique cells from A1 to A100

User · Answer

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

User · Answer

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

User · Answer

UNIQUE  filter Core L8 L27 isblank Core L8 L27  false  query ArrayFormula countif Core L8 L27 Core L8 L27    select Col1 where Col1  lt   0      Core L8 L27   list

User · Answer

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

User · Answer

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

[count] count distinct values in spreadsheet

Examples related to count

Examples related to google-sheets

Examples related to distinct