how to count if a cell value falls between two numbers
I tried to write a formula to count how often the value of a range of cells
falls between two numbers. for example in the chart below: row value 1 25 2 27 3 36 4 37 5 42 I tried the formula =countif(a1:a5,and(35,<40)) and my result was "0" when it should be "2". Can anyone help correct my formula? thanks glenn -- Glenn |
how to count if a cell value falls between two numbers
Try
=SUMPRODUCT(--(B1:B535),--(B1:B5<40)) Hope this helps, Hutch "Glenn" wrote: I tried to write a formula to count how often the value of a range of cells falls between two numbers. for example in the chart below: row value 1 25 2 27 3 36 4 37 5 42 I tried the formula =countif(a1:a5,and(35,<40)) and my result was "0" when it should be "2". Can anyone help correct my formula? thanks glenn -- Glenn |
how to count if a cell value falls between two numbers
There are a few ways to to this:
=SUMPRODUCT((A1:A535)*(A1:A5<40)) =COUNTIF(A1:A5,"35")-COUNTIF(A1:A5,"40") HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Glenn" wrote: I tried to write a formula to count how often the value of a range of cells falls between two numbers. for example in the chart below: row value 1 25 2 27 3 36 4 37 5 42 I tried the formula =countif(a1:a5,and(35,<40)) and my result was "0" when it should be "2". Can anyone help correct my formula? thanks glenn -- Glenn |
how to count if a cell value falls between two numbers
Hi,
=countif(A1:A5,"35")-countif(A1:A5,"<40") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Glenn" wrote in message ... I tried to write a formula to count how often the value of a range of cells falls between two numbers. for example in the chart below: row value 1 25 2 27 3 36 4 37 5 42 I tried the formula =countif(a1:a5,and(35,<40)) and my result was "0" when it should be "2". Can anyone help correct my formula? thanks glenn -- Glenn |
how to count if a cell value falls between two numbers
Ashish, you meant to say 40...
Ryan, since the condition is <40 you need to have the second condition as =40 =COUNTIF(A1:A5,"35")-COUNTIF(A1:A5,"=40") Glenn, if you are using Excel 2007 you can try the below =COUNTIFS(A1:A5,"35",A1:A5,"<40") If this post helps click Yes --------------- Jacob Skaria "Ashish Mathur" wrote: Hi, =countif(A1:A5,"35")-countif(A1:A5,"<40") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Glenn" wrote in message ... I tried to write a formula to count how often the value of a range of cells falls between two numbers. for example in the chart below: row value 1 25 2 27 3 36 4 37 5 42 I tried the formula =countif(a1:a5,and(35,<40)) and my result was "0" when it should be "2". Can anyone help correct my formula? thanks glenn -- Glenn |
All times are GMT +1. The time now is 07:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com