COUNTIF & RANGES

[jdeutsch]

I am trying to figure out to calculate the number of items in a particular column that fall between a lower and upper value. I would also like to sum the values of these cells that fall into this particualr criteria. Can you help?

 

[Dave Patton]

On 2002-09-12 06:01, jdeutsch wrote:
I am trying to figure out to calculate the number of items in a particular column that fall between a lower and upper value. I would also like to sum the values of these cells that fall into this particualr criteria. Can you help?

I put the criteria in E1 and E2

=COUNTIF(C1:C10,">="&E1)-COUNTIF(C1:C10,">"&E2)

=SUMIF(C1:C10,">="&E1)-SUMIF(C1:C10,">"&E2)

Adjust the ranges as necessary.

N.B. check your requirements and the >= and >

 

[jdeutsch]

Tried it but not the result I was looking for. For example: I have a column with the following values 60, 135, 245, 500, 32, 456. I want to know the number of values greater than 240, but less than 499. I also would like to sum those numbers that meet this criteria.

 

[WJReid]

Hi jdeutsch,

Dave is right in his approach. Try the following. I have used range A1:A400.
In a blank cell put:=SUMIF(A1:A400,"<"&500)-SUMIF(A1:A400,"<"&240)
In a second blank cell put:
=COUNTIF(A1:A400,"<"&500)-COUNTIF(A1:A400,"<"&240)

The first will sum the values between 240 and 499 and the second will count the number of occurrences.

Regards,

Bill

 

[jdeutsch]

My values are 60, 120, 240, 240, 300. I want to know the number of occurrences for a value greater than 240 but less than 499. The correct answer is 3, but with the string proposed the first Countif(>=240) yields 3 and the second Countif (<=499) also yields 3 with a total occurrence of 0. This is my predicament. Please help.

 

[WJReid]

Hi jdeutsch,

Both values should be less than "<" as in my last post, not one less than and one greater than ">".

Regards,

Bill

 

[WJReid]

Hi jdeutsch,

Both values should be less than "<" as in my last post, not one less than and one greater than ">".

Regards,

Bill

 

[jdeutsch]

Thanks a million.