COUNTIF & RANGES

jdeutsch

New Member
Joined
Jun 21, 2002
Messages
13
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)

Adjust the ranges as necessary.

N.B. check your requirements and the >= and >
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi jdeutsch,

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

Regards,

Bill
 
Upvote 0
Hi jdeutsch,

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

Regards,

Bill
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top