excel - Distinct value of a column and do a count on each distinct value

Question

Hi I have a column like this.

I know how to count the number of distinct value of this column, but I also want to do a count on what is the number of different distinct value.Is there an easy way to do that?

Thanks.

Answer 1

Considering the following example:

To get the unique values, and the occurrences of each unique value, you can do the following:

The image is tiny, so here is are the formulas to type-in and drag down:

FORMULAS

1. Type in the first cell of column B:

=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))),"",INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0)))

Drag the formula down the column B. You will get a list of unique values.

2. Type in the first cell of column C:

=COUNTIF($A$2:$A$8,B2)

Drag the formula down the column C. You will get the number of occurrences of each unique value.

RESULT

This is what you will get: