greater than or lesser than formulas

[Orrutility secretary]

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!

[tkaplan]

the entire criteria needs to be in quotes:

=SUMIF(C9:Z9,"<2",C9:Z9).

[David Billigmeier]

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:Z9>1),--(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!

[Orrutility secretary]

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:Z9>1),--(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!

[David Billigmeier]

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:Z9>1),--(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!