Countif & Sumif

[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 ?

 

[Teethless mama]

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

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

 

[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

 

[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?

 

[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

 

[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?

 

[JulesHR]

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

 

[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