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

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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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

ADVERTISEMENT

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
Joined
Jul 26, 2002
Messages
317
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
Joined
Jul 26, 2002
Messages
317
Hi jdeutsch,

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

Regards,

Bill
 

Forum statistics

Threads
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.
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
Top