sumif when criteria is a range

  • Thread starter jeremy via OfficeKB.com
  • Start date
J

jeremy via 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.
 
J

JE McGimpsey

One way:

=SUMPRODUCT(--(rng>=0),--(rng<10), rng)

another:

=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

a third

=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
 
R

RagDyeR

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!
=====================================================

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.
 
J

jeremy via OfficeKB.com

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....
 
R

RagDyeR

John,

Countif ?
Confusing the OP's 2 posts?<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

One way:

=SUMPRODUCT(--(rng>=0),--(rng<10), rng)

another:

=COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

a third

=COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
 
R

RagDyeR

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!
=====================================================


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....
 
J

jeremy via OfficeKB.com

thanks--that was brillant...
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.
Click to expand...
 
Top