| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting # of cells
I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
=COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129")
or =SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ronnie" wrote in message ... I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
I think you mistyped on your COUNTIF
I believe it should be: =COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "129") -- ** John C ** "Bernard Liengme" wrote: =COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129") or =SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ronnie" wrote in message ... I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
The sumproduct formulat worked. THANKS!!
-- Ronnie "Bernard Liengme" wrote: =COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129") or =SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ronnie" wrote in message ... I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
the column being 'A', it could be written this way:
=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120") "Ronnie" wrote: I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
I am so close, but it's not working. I think it's the minus sign in the
formula. Any other suggestions. -- Ronnie "Ryan Martin" wrote: the column being 'A', it could be written this way: =COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120") "Ronnie" wrote: I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
What makes you think that the minus sign is wrong? The formula gives the
right result if the values are integer and the "between" is intended to be inclusive. It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in case the numbers are not necessarily integer. Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is intended to be exclusive, not inclusive. -- David Biddulph "Ronnie" wrote in message ... I am so close, but it's not working. I think it's the minus sign in the formula. Any other suggestions. -- Ronnie "Ryan Martin" wrote: the column being 'A', it could be written this way: =COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120") "Ronnie" wrote: I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
I'm not sure what was wrong. I was unable to get the counts to come out
properly. I did get the "sumproduct" formula to work. I would like to know why the other formula was not working, but it wasn't. -- Ronnie "David Biddulph" wrote: What makes you think that the minus sign is wrong? The formula gives the right result if the values are integer and the "between" is intended to be inclusive. It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in case the numbers are not necessarily integer. Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is intended to be exclusive, not inclusive. -- David Biddulph "Ronnie" wrote in message ... I am so close, but it's not working. I think it's the minus sign in the formula. Any other suggestions. -- Ronnie "Ryan Martin" wrote: the column being 'A', it could be written this way: =COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120") "Ronnie" wrote: I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting # of cells
We may need a bit more information than "was not working".
What values did you have in the cells, which formula were you using, what answer did you get, and what answer did you expect? -- David Biddulph "Ronnie" wrote in message ... I'm not sure what was wrong. I was unable to get the counts to come out properly. I did get the "sumproduct" formula to work. I would like to know why the other formula was not working, but it wasn't. -- Ronnie "David Biddulph" wrote: What makes you think that the minus sign is wrong? The formula gives the right result if the values are integer and the "between" is intended to be inclusive. It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in case the numbers are not necessarily integer. Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is intended to be exclusive, not inclusive. -- David Biddulph "Ronnie" wrote in message ... I am so close, but it's not working. I think it's the minus sign in the formula. Any other suggestions. -- Ronnie "Ryan Martin" wrote: the column being 'A', it could be written this way: =COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120") "Ronnie" wrote: I need a formula to count the number of cells in a column that contain the numbers between 120 and 129. Thanks for any input! -- Ronnie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting the number of cells between 2 seperate cells | Excel Discussion (Misc queries) | |||
| Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
| Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
Hybrid Mode
