Countif for numeric range
Posted by snayar on January 11, 2002 11:40 AM
I am trying to come up with a formula for a countif with multiple criteria within the same data column
For example, with this data:
3
4
5
6
8
12
15
I want to count the number of occurances for data between 5 and 10, etc. How can I do this?
thanks
Posted by Jason on January 11, 2002 11:48 AM
Well I'm sure there are a lot of ways to do it but, this is how I would.
Assume the data is in col A, then in col b enter
=if(and(a1>5,a1<10),1,0)
Then all you have to do is sum col b and that will tell you how many numbers are between 5 and 10.
Posted by Aladin Akyurek on January 11, 2002 12:26 PM
Try:
=COUNTIF(A2:A8,">"&E1)-COUNTIF(A2:A8,">="&E2)
where A2:A8 houses the numbers of interest, E1 the lower limit criterion, and E2 the lower limit criterion.
Note. I assumed an exclusive between. Adjust to what you want COUNTIF to include.
Aladin
=======
Posted by Carterjeff on January 11, 2002 6:58 PM
I think Aladin is on the right track, but maybe has a typo. I would use
=COUNTIF(E6:E12,">=5")-COUNTIF(E6:E12,">=10")
(use the >= to include 5 and 10, the > sign for between 5 and 10). You can replace the values 5 and 10 with cell references as aladin did, then type the values (ranges) in those cells
jeff
