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

[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

 

[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

 

[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

============================================================

 

[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