Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
In article ,
"RagDyeR" wrote: Countif ? Yup - should have been SUMIF() |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with AND for range and criteria | Excel Worksheet Functions | |||
SUMIF using two columns in both Range and Criteria | Excel Discussion (Misc queries) | |||
SUMIF using two columns in both Range and Criteria | Excel Worksheet Functions | |||
SUMIF with only one criteria cell within range | Excel Discussion (Misc queries) | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |