I'm using the SUMIF function to add numbers in a range that meet a given
criteria. The criteria I need to use is a range of numbers (>=1 and <=5, >=6
and <=10, etc).

While I have been able to perform this calculation by hard coding in the
parameters (">=1" and ">5" for example), I am unable to complete the formula
if I want to use cell references for the criteria.

Here is the whole formula that currently works by hard coding in the
criteria:

=COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")

In other words, I want to use >=G2 rather than >=1 and use >I2 rather than
>5.

How do I accomplish this?

Thanks,
David

Try

=COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)

otherwise excel sees it as you are looking for the text G2 and I2


--

Regards,

Peo Sjoblom

"David Lipetz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using the SUMIF function to add numbers in a range that meet a given
> criteria. The criteria I need to use is a range of numbers (>=1 and <=5,
>=6
> and <=10, etc).
>
> While I have been able to perform this calculation by hard coding in the
> parameters (">=1" and ">5" for example), I am unable to complete the
formula
> if I want to use cell references for the criteria.
>
> Here is the whole formula that currently works by hard coding in the
> criteria:
>
> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
>
> In other words, I want to use >=G2 rather than >=1 and use >I2 rather than
> >5.
>
> How do I accomplish this?
>
> Thanks,
> David
>
>

Correction to my post:
I am using COUNTIF not SUMIF.


"David Lipetz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using the SUMIF function to add numbers in a range that meet a given
> criteria. The criteria I need to use is a range of numbers (>=1 and <=5,
> >=6 and <=10, etc).
>
> While I have been able to perform this calculation by hard coding in the
> parameters (">=1" and ">5" for example), I am unable to complete the
> formula if I want to use cell references for the criteria.
>
> Here is the whole formula that currently works by hard coding in the
> criteria:
>
> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
>
> In other words, I want to use >=G2 rather than >=1 and use >I2 rather than
> >5.
>
> How do I accomplish this?
>
> Thanks,
> David
>

Thanks Peo! That worked, but now I have another problem.

I wanted to create this as an array formula so that the criteria is selected
from each row: G2 & I2, G3 & I3, and so forth. With the & in front of the
cell reference, it uses that same reference throughout the array.


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Try
>
> =COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)
>
> otherwise excel sees it as you are looking for the text G2 and I2
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "David Lipetz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I'm using the SUMIF function to add numbers in a range that meet a given
>> criteria. The criteria I need to use is a range of numbers (>=1 and <=5,
>>=6
>> and <=10, etc).
>>
>> While I have been able to perform this calculation by hard coding in the
>> parameters (">=1" and ">5" for example), I am unable to complete the
> formula
>> if I want to use cell references for the criteria.
>>
>> Here is the whole formula that currently works by hard coding in the
>> criteria:
>>
>> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
>>
>> In other words, I want to use >=G2 rather than >=1 and use >I2 rather
>> than
>> >5.
>>
>> How do I accomplish this?
>>
>> Thanks,
>> David
>>
>>
>
>

I am not sure I understand, but maybe

=SUM(COUNTIF(Revised!$A$2:$A$336,">="&G2:G336))-SUM(COUNTIF(Revised!$A$2:$A$
336,">"&I2:I336))

needs to be array entered with ctrl + shift & enter




--

Regards,

Peo Sjoblom

"David Lipetz" <(E-Mail Removed)> wrote in message
news:uv%(E-Mail Removed)...
> Thanks Peo! That worked, but now I have another problem.
>
> I wanted to create this as an array formula so that the criteria is
selected
> from each row: G2 & I2, G3 & I3, and so forth. With the & in front of the
> cell reference, it uses that same reference throughout the array.
>
>
> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Try
> >
> >
=COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)
> >
> > otherwise excel sees it as you are looking for the text G2 and I2
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "David Lipetz" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> I'm using the SUMIF function to add numbers in a range that meet a
given
> >> criteria. The criteria I need to use is a range of numbers (>=1 and
<=5,
> >>=6
> >> and <=10, etc).
> >>
> >> While I have been able to perform this calculation by hard coding in
the
> >> parameters (">=1" and ">5" for example), I am unable to complete the
> > formula
> >> if I want to use cell references for the criteria.
> >>
> >> Here is the whole formula that currently works by hard coding in the
> >> criteria:
> >>
> >> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
> >>
> >> In other words, I want to use >=G2 rather than >=1 and use >I2 rather
> >> than
> >> >5.
> >>
> >> How do I accomplish this?
> >>
> >> Thanks,
> >> David
> >>
> >>
> >
> >
>
>

Thanks again! The actual formula is:
=SUMIF(Revised!$A$2:$A$335,">="&A2:A8,Revised!$L$2:$L$335)-SUMIF(Revised!$A$2:$A$335,">"&C2:C8,Revised!$L$2:$L$335)


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am not sure I understand, but maybe
>
> =SUM(COUNTIF(Revised!$A$2:$A$336,">="&G2:G336))-SUM(COUNTIF(Revised!$A$2:$A$
> 336,">"&I2:I336))
>
> needs to be array entered with ctrl + shift & enter
>
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "David Lipetz" <(E-Mail Removed)> wrote in message
> news:uv%(E-Mail Removed)...
>> Thanks Peo! That worked, but now I have another problem.
>>
>> I wanted to create this as an array formula so that the criteria is
> selected
>> from each row: G2 & I2, G3 & I3, and so forth. With the & in front of the
>> cell reference, it uses that same reference throughout the array.
>>
>>
>> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Try
>> >
>> >
> =COUNTIF(Revised!$A$2:$A$336,">="&G2)-COUNTIF(Revised!$A$2:$A$336,">"&I2)
>> >
>> > otherwise excel sees it as you are looking for the text G2 and I2
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> > "David Lipetz" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> I'm using the SUMIF function to add numbers in a range that meet a
> given
>> >> criteria. The criteria I need to use is a range of numbers (>=1 and
> <=5,
>> >>=6
>> >> and <=10, etc).
>> >>
>> >> While I have been able to perform this calculation by hard coding in
> the
>> >> parameters (">=1" and ">5" for example), I am unable to complete the
>> > formula
>> >> if I want to use cell references for the criteria.
>> >>
>> >> Here is the whole formula that currently works by hard coding in the
>> >> criteria:
>> >>
>> >> =COUNTIF(Revised!$A$2:$A$336,">=1")-COUNTIF(Revised!$A$2:$A$336,">5")
>> >>
>> >> In other words, I want to use >=G2 rather than >=1 and use >I2 rather
>> >> than
>> >> >5.
>> >>
>> >> How do I accomplish this?
>> >>
>> >> Thanks,
>> >> David
>> >>
>> >>
>> >
>> >
>>
>>
>
>