20

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`
Improve this question
1
  • 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.
    – Jim K
    Commented Aug 18, 2017 at 2:51

2 Answers 2

Reset to default
17

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.

pivot table

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.

Improve this answer
2
  • 3
    For the pivot table, you don't need the Count column, you can drag Color to both Row and Data Fields, then double click it in the Data Fields and select Count instead of Sum: superuser.com/a/1614716/98217
    – Dario Seidl
    Commented Jul 21, 2022 at 13:04
  • @DarioSeidl this should be the answer, thanks!
    – waldo22
    Commented Jun 13 at 1:25
2

Another simple option:

Assumptions:

  • Your data is sorted
  • For the example below your data is in column A (adjust accordingly).
  • For the example below you enter the formula on row 2.

Add a new column with the formula below created on row 2 and copy it to all other rows.

IF(A2 == A1, 0, 1)

Then you get a 1 in that column ever time a value differs from the row above. Then you can just sum the 1 values:

SUM(B2:B?)

Your spreadsheet would look like this:

 1  "A" | "B" 
 -------------
 2  VALA   1   <-- IF(A2 == A1, 0, 1)
 3  VALA   0
 4  VALA   0
 5  VALB   1
 6  VALB   0
 7  VALC   1
 8  VALD   1
 9  VALD   0
10         4   <-- SUM(B2:B9)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .