COUNTIF Function with mulitple 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]

If your random numbers are in column A then put your upper and lower limits
for each range in columns C and D, e.g. C2 =0, D2 = 9, C3=10, D3=19 and so on
then in E2 use this formula copied down

=COUNTIF(A:A,">="&C2)-COUNTIF(A:A,">"&D2)

 

[Bill Kuunders]

one way

Set up your class borders in two columns B and C,

B1 has 0 C1 has 9
B2 has 10 C2 has 19
b3 has 30 C3 has 29
etc


enter =SUMPRODUCT((A$1:A$1000>=B1)*(A$1:A$1000<=C1))

in cell D1 and extend down to D10

p.s.
the class of 0 to 9 will include any empty cells in the range A1 to A1000

 

[Guest]

As long as your ranges are always groups of 10 as you posted

Then..with
A1:A100 containing your list of numbers

Try this:
C1: 0
C2: 10
C3: 20
etc

E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1))
Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Corey]

I don't think I'm phrasing this in the most effective way. When you
begin to type a COUNTIF statement, the formula pops up with something
as follows:

=COUNTIF([Range],[Criteria])

I'm wanting to test for a range as part of my criteria. Something
like:

=COUNTIF(B59:B83,">19" & "<30")

To me, that would imply that for each cell between B59 and B83, add
one to the count for any cells that contain some number from 20-29.

It doesn't work though in my spreadsheet. I hope that makes my goal
more understandable. Thanks for all of your help!

 

[Lori]

You could also try:

=FREQUENCY([Range],{10,20,30,40,50})

This needs to be array-entered (with ctrl+shift+enter) in the range.

I don't think I'm phrasing this in the most effective way. When you
begin to type a COUNTIF statement, the formula pops up with something
as follows:

=COUNTIF([Range],[Criteria])

I'm wanting to test for a range as part of my criteria. Something
like:

=COUNTIF(B59:B83,">19" & "<30")

To me, that would imply that for each cell between B59 and B83, add
one to the count for any cells that contain some number from 20-29.

It doesn't work though in my spreadsheet. I hope that makes my goal
more understandable. Thanks for all of your help!

As long as your ranges are always groups of 10 as you posted

Then..with
A1:A100 containing your list of numbers

Try this:
C1: 0
C2: 10
C3: 20
etc

E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1))
Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Your description was fine.
I guess what you need to hear is that COUNTIF won't work exactly the way you
want it to. I wish it could use multiple criteria (like what you
posted)....but it can't.

The responses you got represent some of the ways that Excel CAN deliver the
values you're looking for.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I don't think I'm phrasing this in the most effective way. When you
begin to type a COUNTIF statement, the formula pops up with something
as follows:

=COUNTIF([Range],[Criteria])

I'm wanting to test for a range as part of my criteria. Something
like:

=COUNTIF(B59:B83,">19" & "<30")

To me, that would imply that for each cell between B59 and B83, add
one to the count for any cells that contain some number from 20-29.

It doesn't work though in my spreadsheet. I hope that makes my goal
more understandable. Thanks for all of your help!

As long as your ranges are always groups of 10 as you posted

Then..with
A1:A100 containing your list of numbers

Try this:
C1: 0
C2: 10
C3: 20
etc

E1: =SUMPRODUCT(--(INT($A$1:$A$100/10)*10=C1))
Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP