I'm still an excel newbie--I searched the archives but couldn't find the
answer...
How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
Thanks.
--
Message posted via http://www.officekb.com
I'm still an excel newbie--I searched the archives but couldn't find the
answer...
How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
Thanks.
--
Message posted via http://www.officekb.com
One way:
=SUMPRODUCT(--(rng>=0),--(rng<10), rng)
another:
=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
a third
=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
In article <[email protected]>,
"jeremy via OfficeKB.com" <[email protected]> wrote:
> I'm still an excel newbie--I searched the archives but couldn't find the
> answer...
>
> How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>
> Thanks.
Sorry, I forgot to specify that I want to sum a second column if the first
meets the criteria range...
Eg. =SUMIF($BE$9:$BE$272, 0=<X<10, ($BF$9:$BF$272))
Of course, 0=<X<10 doesn't work.....
Thanks....
JE McGimpsey wrote:
>One way:
>
> =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
>
>another:
>
> =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
>
>a third
>
> =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
>
>> I'm still an excel newbie--I searched the archives but couldn't find the
>> answer...
>>
>> How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>>
>> Thanks.
--
Message posted via http://www.officekb.com
The Sumproduct formula that you quoted in your earlier post will do the same
job for this by just adding an additional argument:
=SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)*$W$9:$W$272)
OR
=SUMIF($W$9:$W$272,">0")-SUMIF($W$9:$W$272,">=10")
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
"jeremy via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
I'm still an excel newbie--I searched the archives but couldn't find the
answer...
How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
Thanks.
--
Message posted via http://www.officekb.com
John,
Countif ?
Confusing the OP's 2 posts?<g>
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
One way:
=SUMPRODUCT(--(rng>=0),--(rng<10), rng)
another:
=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
a third
=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
In article <[email protected]>,
"jeremy via OfficeKB.com" <[email protected]> wrote:
> I'm still an excel newbie--I searched the archives but couldn't find the
> answer...
>
> How do I use sumif function when the criteria is a range (eg. >=0 to
<10 ).
>
> Thanks.
Again, you can use your original formula and just change the range that you
wish to total:
=SUMPRODUCT(($BE$9:$BE$272>=0)*($BE$9:$BE$272<10)*$BF$9:$BF$272)
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
"jeremy via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
Sorry, I forgot to specify that I want to sum a second column if the first
meets the criteria range...
Eg. =SUMIF($BE$9:$BE$272, 0=<X<10, ($BF$9:$BF$272))
Of course, 0=<X<10 doesn't work.....
Thanks....
JE McGimpsey wrote:
>One way:
>
> =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
>
>another:
>
> =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
>
>a third
>
> =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
>
>> I'm still an excel newbie--I searched the archives but couldn't find the
>> answer...
>>
>> How do I use sumif function when the criteria is a range (eg. >=0 to
<10 ).
>>
>> Thanks.
--
Message posted via http://www.officekb.com
In article <#[email protected]>,
"RagDyeR" <[email protected]> wrote:
> Countif ?
Yup - should have been SUMIF()
thanks--that was brillant...
RagDyeR wrote:
>The Sumproduct formula that you quoted in your earlier post will do the same
>job for this by just adding an additional argument:
>
>=SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)*$W$9:$W$272)
>
>OR
>
>=SUMIF($W$9:$W$272,">0")-SUMIF($W$9:$W$272,">=10")
>
>I'm still an excel newbie--I searched the archives but couldn't find the
>answer...
>
>How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
>
>Thanks.
--
Message posted via http://www.officekb.com
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks