Countif & Sumif

J

JulesHR

how do i doa formula that will count or sum a bunch of numbers that are
between a particular range. i.e. >100 but < 200 ?
 
T

Teethless mama

For count:
=SUM(COUNTIF(A:A,{">100",">=200"})*{1,-1})

For sum:
=SUM(SUMIF(A:A,{">100",">=200"})*{1,-1})
 
B

Bernie Deitrick

Jules,

=SUMIF(Rangewithnumbers,">100")-SUMIF(Rangewithnumbers,">200")
=COUNTIF(Rangewithnumbers,">100")-COUNTIF(Rangewithnumbers,">200")

Though you may want:

=SUMIF(Rangewithnumbers,">=100")-SUMIF(Rangewithnumbers,">200")
=SUMIF(Rangewithnumbers,">100")-SUMIF(Rangewithnumbers,">=200")
=SUMIF(Rangewithnumbers,">=100")-SUMIF(Rangewithnumbers,">=200")

boundaries issues....

HTH,
Bernie
MS Excel MVP
 
J

JulesHR

Ok, I get it thanks. Now what I want to do is add all the values in the range
i.e. if there was a number of 150 then 50 would be counted in the range of
100 to 200.

Does that make sense?
 
B

Bernie Deitrick

Would your desired result for 125 be 25 or 75? 150 is 50 greater than 100, and 50 less than 200,
so I'm not sure what you want...

Either

=Number - LowerLimit

or

=UpperLimit - Number

will give you the answer you want.

HTH,
Bernie
MS Excel MVP
 
J

JulesHR

Ok, I get it thanks. Now what I want to do is add all the values in the range
i.e. if there was a number of 150 then 50 would be counted in the range of
100 to 200, or if 125 was the number then 25 would be counted.

Does that make sense?
 
J

JulesHR

for 125 it should count 25. The formula you gave in you last post, where
would they be entered? I don't follow.
 
B

Bernie Deitrick

Jules,

Let's say that you enter the 125 into cell A2. In another cell, use the formula

=A2-100

HTH,
Bernie
MS Excel MVP
 
Top