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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Dave Patton

Well-known Member
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 >

jdeutsch

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

Active Member
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

New Member

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

Active Member
Hi jdeutsch,

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

Regards,

Bill

WJReid

Active Member
Hi jdeutsch,

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

Regards,

Bill

jdeutsch

New Member
Thanks a million.

Replies
2
Views
328
Replies
29
Views
522
Replies
5
Views
170
Replies
9
Views
383
Replies
1
Views
55

1,148,280
Messages
5,745,823
Members
423,981
Latest member
ph1l

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.

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