| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count the cell contain a specific numbers in a range | Excel Discussion (Misc queries) | |||
| Count unique numbers in a range with a given criteria | Excel Discussion (Misc queries) | |||
| Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
| get a count of numbers whose value falls within a given range | Excel Worksheet Functions | |||
| How do I count numbers in a range that are greater than and less . | Excel Worksheet Functions | |||
Hybrid Mode
