CountIf with multiple criteria

[Corey]

I'm trying to set up some countif statements that will count the
number of cells within a given range that contain number within a
given range.

Ex. One column has a list of random numbers from 0-100. The next
column will count the number of numbers that fall into a given
range.
How many of the cells within the first column contain numbers between
0 and 9? 10 and 19? So on and so forth...

Any help on this would be greatly appreciated.

 

[Guest]

=COUNTIF(A1:A100,"<10")
=COUNTIF(A1:A100,"<20")-COUNTIF(A1:A100,"<9")

 

[Ken Johnson]

Hi Corey,

Assuming your random numbers are integers and in column A then this
formula will give you the count of 1 to 9...

=COUNTIF($A:$A,"<10")

and this formula in the cell below then filled down will give the
other counts...

=COUNTIF($A:$A,"<"&ROW(A2)*10+IF(ROW(A2)=10,1,0))-
SUM(INDIRECT(ADDRESS(ROW()-
ROW(A2)+1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Ken Johnson