CountIf with multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Corey - 27 Jan 2007 21:38 GMT

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.

Reply to this Message

Gary''s Student - 27 Jan 2007 22:09 GMT

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

Signature

Gary's Student
gsnu200702

> 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
[quoted text clipped - 7 lines]
>
> Any help on this would be greatly appreciated.

Reply to this Message

Ken Johnson - 28 Jan 2007 03:59 GMT

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

Reply to this Message