Counting number of unique values/findings

[Saturn]

I use the following formula to count the number of unique values:

={SUM(1/COUNTIF(Range;Range))}

This one works as far as there aren’t any blank cells. The result is then: #DIV/0!
How could I construct the formula to ignore blank cells?

Saturn

[Fotis1991]

Hi

Pls, give a try to this.

=SUM(IF(FREQUENCY(A1:A10;A1:A10)=1;FREQUENCY(A1:A10;A1:A10)))

Replace, semi-colons, to comma, if you have to do it.

[Saturn]

I'am afraid this doesn't work.
The answer is 0, with and without blank cells.

[Fotis1991]

Take a look to the example, pls.

[rvasquez]

Hello Saturn,

=IFERROR(SUM(1/COUNTIF(Range,Value)),"")

Should do it.

Thanks,

RVASQUEZ

[Saturn]

Hello Fotis 1991

Thanks for your answer, but the result should be 5 and not 3 (5, 7, 10, 20, 25). Could you look into this?

(rvasquez sugestion is not working. IFERROR gives a blank cell if one of the cells is blank... )

[Fotis1991]

Count 3, because it counts the values, that appears, only one time(5.7.25)

Just now the only way, that i can thing for what you really ask for, is using a helper(and hidden)column)

So in B1, put this formula and copy down.

=COUNTIF($A1:$A$10;A1) 

Then, this formula, will gives you, 5!

=COUNTIF(B1:B10;"=1") 

[ChemistB]

Correction on Fotis's formula

=COUNTIF($A$1:$A1;A1) dragged down

Edit: Nevermind. I see Folis just did it the reverse. Either way will work. My mistake.

[Saturn]

Great, this works. A creative solution!