Home |
Search |
Today's Posts |
#1
|
|||
|
|||
greater than or lesser than formulas
This is simple, I'm sure but I cannot get this formula: I have a row of
figures ranging from 1.00 thru 6.00. All I want to do is start with the first cell and determine if the range is between 1:00 but less than<2.00, if it is add the row across for all cells that this range is applicable to, otherwise "0". next cell between ?2:00 but less than <3.00 etc, etc. I can get the formula to work with just one range - but for the life of me can't get it work with a varied range. My formula is =SUMIF(C9:Z9,""<2,C9:Z9). Help! |
#2
|
|||
|
|||
In your descrption you said you were looking for greater than 1 and less than
2, but your formula appears you are going for strictly less than 2... so i'm not quite sure what you need exactly, but this formula will give you 1 and <2: =SUMPRODUCT(--(C9:Z91),--(C9:Z9<2),C9:Z9) -- Regards, Dave "Orrutility secretary" wrote: This is simple, I'm sure but I cannot get this formula: I have a row of figures ranging from 1.00 thru 6.00. All I want to do is start with the first cell and determine if the range is between 1:00 but less than<2.00, if it is add the row across for all cells that this range is applicable to, otherwise "0". next cell between ?2:00 but less than <3.00 etc, etc. I can get the formula to work with just one range - but for the life of me can't get it work with a varied range. My formula is =SUMIF(C9:Z9,""<2,C9:Z9). Help! |
#3
|
|||
|
|||
the entire criteria needs to be in quotes: =SUMIF(C9:Z9,"<2",C9:Z9). -- tkaplan ------------------------------------------------------------------------ tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987 View this thread: http://www.excelforum.com/showthread...hreadid=471142 |
#4
|
|||
|
|||
Yes - you were correct, and Yes that worked. So from looking at the formula
I'm assuming the "--" sets an array? Thanks again "David Billigmeier" wrote: In your descrption you said you were looking for greater than 1 and less than 2, but your formula appears you are going for strictly less than 2... so i'm not quite sure what you need exactly, but this formula will give you 1 and <2: =SUMPRODUCT(--(C9:Z91),--(C9:Z9<2),C9:Z9) -- Regards, Dave "Orrutility secretary" wrote: This is simple, I'm sure but I cannot get this formula: I have a row of figures ranging from 1.00 thru 6.00. All I want to do is start with the first cell and determine if the range is between 1:00 but less than<2.00, if it is add the row across for all cells that this range is applicable to, otherwise "0". next cell between ?2:00 but less than <3.00 etc, etc. I can get the formula to work with just one range - but for the life of me can't get it work with a varied range. My formula is =SUMIF(C9:Z9,""<2,C9:Z9). Help! |
#5
|
|||
|
|||
The Sumproduct() function sets the array. The "--" changes TRUE/FALSE to 1/0
so a multiplication can take place between the array's -- Regards, Dave "Orrutility secretary" wrote: Yes - you were correct, and Yes that worked. So from looking at the formula I'm assuming the "--" sets an array? Thanks again "David Billigmeier" wrote: In your descrption you said you were looking for greater than 1 and less than 2, but your formula appears you are going for strictly less than 2... so i'm not quite sure what you need exactly, but this formula will give you 1 and <2: =SUMPRODUCT(--(C9:Z91),--(C9:Z9<2),C9:Z9) -- Regards, Dave "Orrutility secretary" wrote: This is simple, I'm sure but I cannot get this formula: I have a row of figures ranging from 1.00 thru 6.00. All I want to do is start with the first cell and determine if the range is between 1:00 but less than<2.00, if it is add the row across for all cells that this range is applicable to, otherwise "0". next cell between ?2:00 but less than <3.00 etc, etc. I can get the formula to work with just one range - but for the life of me can't get it work with a varied range. My formula is =SUMIF(C9:Z9,""<2,C9:Z9). Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
if greater or lesser than | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |