MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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