Count Numbers Only Once in a Range
 

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

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

Count Numbers Only Once in a Range
 

=SUMPRODUCT(1/(COUNTIF($A$1:$A$23,A1:$A$23)),A1:A23)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iperlovsky" wrote in message
...
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

Count Numbers Only Once in a Range
 

Try this:

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

--
Biff
Microsoft Excel MVP


"iperlovsky" wrote in message
...
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

Count Numbers Only Once in a Range
 

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?

"Bernard Liengme" wrote:

=SUMPRODUCT(1/(COUNTIF($A$1:$A$23,A1:$A$23)),A1:A23)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iperlovsky" wrote in message
...
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

Count Numbers Only Once in a Range
 

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?

"Jacob Skaria" wrote:

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

Count Numbers Only Once in a Range
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iperlovsky" wrote in message
...
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?

"Bernard Liengme" wrote:

=SUMPRODUCT(1/(COUNTIF($A$1:$A$23,A1:$A$23)),A1:A23)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"iperlovsky" wrote in message
...
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

Count Numbers Only Once in a Range
 

This should count everything except blanks. Try this in a new workbook
--
If this post helps click Yes
---------------
Jacob Skaria


"iperlovsky" wrote:

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?

"Jacob Skaria" wrote:

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