View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Jacob Skaria Jacob Skaria is offline
external usenet poster
 
Posts: 8,520
Default Count Numbers Only Once in a Range

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
---------------
Jacob Skaria


"iperlovsky" wrote:

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