How to count amount of different values when same values occure?

Question

I would like to know how I can count the amount of different values in a column with some values that are the same?

My issue is that sometimes I have a column of varing numbers but there are some that are the same. I am not interested in the total count but in the count of numbers that are different.

For example:

1,7,2,1,8,8,12,4,8,9,3,5,1,9

The answer should be:

1,1,1,2,3,4,5,7,8,8,8,9,9,12

SO the result is:

1,2,3,4,5,7,8,9,12 = count= 9

How can I do this in Excel?

Answer 1

For numeric values only, as per this question, you can use FREQUENCY function like this

=SUM(IF(FREQUENCY(A2:A100,A2:A100),1))

confirmed with CTRL+SHIFT+ENTER

Answer 2

A way to do it w/o a formula, is to select the column and add a filter. An advanced filter has an option to filter out duplicates. Once enabled, you'll see a count in the lower left corner. At least that's how it works in Excel 2010.

Answer 3

you want to count the number of distinct values:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

source: https://superuser.com/questions/189762/how-to-count-number-of-distinct-values-in-a-range