Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL | Excel Discussion (Misc queries) | |||
displaying text in a cell if a another cell falls in a range | Excel Worksheet Functions | |||
count no. of dates in a column that falls on certain month & year | Excel Worksheet Functions | |||
how to count if the value falls between a date range | Excel Discussion (Misc queries) | |||
get a count of numbers whose value falls within a given range | Excel Worksheet Functions |