COUNTIF....how to use >=0 but <9 as CRITERIA

J

Jay

I cannot figure out how to use two criteria with the COUNTIF function. Here
is what
I need to accomplish:

I have a list of 2000 numbers in column A, the numbers are between 0 and
99(inclusive).

I want to see how many numbers are in the following ranges:
1. greater than or equal to 0, less than 9
2. greater than or equal to 10, less than 19
3. greater than or equal to 20, less than 29
..
..
..
..
..
..
9.greater than or equal to 90, less than 99



Using the COUNTIF function how do you nest >=0 but <9 to achieve the results
for my first range?


I have tried the following two versions of the function to no avail:

=COUNTIF(A1:A2000,">=0,<9")

=COUNTIF(A1:A2000,"AND(>=0,<9)")


Does the COUNTIF function only allow one variable as the criteria??

Any suggestions or a solution would be appreciated.

THANKS
 
M

Mike H

Hi,

If you want to use countif you use 2 of them like this

=COUNTIF(A1:A34,">=0")-COUNTIF(A1:A34,">9")

The first counts everything >= to your lower number and the second subtracts
everything larger than your largest.
Mike
 
H

HARSHAWARDHAN. S .SHASTRI

Hi Jay,

You can use Frequency function .

Do one thing in column b1: b10 put values 9,19,29 --- 99 then in cell c1
put this formula

=FREQUENCY(a1:a2000,b1:b10) and copy down upto c10 and then enter as array
formula .

note : select the range c1:c10 starting with the formula cell. Press F2,
and then press CTRL+SHIFT+ENTER.

Harshawardhan . Shastri

India

============================================================
 
J

Jay

Harshawardhan and Mike thanks for the info.

Mike yours works as suggested.

thanks for the info on FREQUENCY function.

Harshawardhan is it possible for you to send me an excel sheet with what you
describe in it?

Possibly make a list of 30 or so random numbers between 0 and 99..write the
coding for the frequency function and email it to me.

If you have the time?

My email is

(e-mail address removed)

THANKS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top