# COUNTIF & RANGES

#### jdeutsch

##### New Member
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?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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)

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

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.

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

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.

Hi jdeutsch,

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

Regards,

Bill

Hi jdeutsch,

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

Regards,

Bill

Thanks a million.

Replies
5
Views
244
Replies
1
Views
119
Replies
8
Views
697
Replies
1
Views
278
Replies
4
Views
415

1,217,675
Messages
6,137,925
Members
450,099
Latest member
Pushbutton

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back