LibreOffice: count distinct values from column

Question

I have a column with some repeatable values, like color names in a list of a material objects. How could I count a number of each unique value occurrences (e.g. for the 100 rows given there are 5 red, 15 green and 80 black)? I cant use COUNTIF since I don't know all the "colors" that may occure.

In SQL-way I would do something like

SELECT count(`color`), `color` FROM `MyTable` goup by `color`

Solution

A spreadsheet cannot do it as easily as with SQL, but here are two solutions.

Method 1 - Pivot Table

Make sure the first row of the column contains a label, for example Color. In the next column, set the label to Count. Enter a count of 1 for all colors.

Color  Count
red    1
green  1
red    1

Then, select the two columns and go to Data -> Pivot Table -> Create. Drag Color to Row Fields, and drag Count to Data Fields.

Method 2 - Filter

1. Copy the column data, and paste into column A of a new sheet.
2. Go to Data -> More Filters -> Standard Filter.
3. Change Field Name to - none -. Expand Options and check No duplicates. Press OK.
4. In B1, enter the formula =COUNTIF($Sheet1.G1:G100,"="&A1). Change "G" to the column you used on Sheet 1.
5. Drag the formula down.

Links for getting distinct values are at https://stackoverflow.com/a/38286032/5100564.

Comments

[Jim K]

At first I misunderstood this question as counting the number of distinct values (3 in your example). Instead, the SQL shows that you are asking about counting grouped values. Perhaps you could edit the title.