sumif when criteria is a range
 

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 ,
"jeremy via OfficeKB.com" 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.

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" wrote in message
...
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

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

John,

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

Regards,

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

"JE McGimpsey" wrote in message
...
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 ,
"jeremy via OfficeKB.com" 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" wrote in message
...
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 ,
"RagDyeR" 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