Count Numbers Only Once in a Range

[iperlovsky]

Does anyone know a formula that counts numbers in a range only once when they
appear. For example: Range includes 1.34 three times, 0.80 five times, and
0.27 15 times. The count formula counts the range 23 times rather than the 3
times I am looking for. Any help is greatly appreciated.

Thanks

 

[Jacob Skaria]

The below formula will give you the distinct count for range A2:A100.

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

If this post helps click Yes

 

[Bernard Liengme]

=SUMPRODUCT(1/(COUNTIF($A$1:$A$23,A1:$A$23)),A1:A23)
best wishes

 

[T. Valko]

Try this:

=SUM(--(FREQUENCY(A1:A25,A1:A25)>0))

 

[iperlovsky]

This formula returns a count of 1.34 for my first distinct number, 2.14 for
the second and 2.41 for the third. Any idea how we could troubleshoot this?

 

[iperlovsky]

This formula works near perfectly. It just does not count 0 correctly - like
for example when one of the distinct numbers in the range is 0. Any
suggestions?

 

[Bernard Liengme]

I do not follow. I typed the numbers in your example into a column A.
My formula gave me the result of 1.34 + 0.08 + 0.27 = 1.69
Send me a sample file - get my email from bottom of my website
best wishes

 

[Jacob Skaria]

This should count everything except blanks. Try this in a new workbook