Function countif with conditions
 

I have a list of numbers (2000 lines). I need to count the numbers of
the values in different ranges. eg <= $10.00, $10.01 to $100.00,
$100.01 to $500.00, etc.

The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257.

But for the next range, formula
=COUNTIF(H2:H2001,AND("10","<=100")) doesn't work.

What's the problem? How can I get this to work?


TIA

Try the formula =COUNTIF(H2:H2001,"=10")-COUNTIF(B2:B5,"100")


"Gordon" wrote:

I have a list of numbers (2000 lines). I need to count the numbers of
the values in different ranges. eg <= $10.00, $10.01 to $100.00,
$100.01 to $500.00, etc.

The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257.

But for the next range, formula
=COUNTIF(H2:H2001,AND("10","<=100")) doesn't work.

What's the problem? How can I get this to work?


TIA

=SUMPRODUCT((H2:H200110)*(H2:H2001<=100))

--
Regards,
Dave
<!--


"Gordon" wrote:

I have a list of numbers (2000 lines). I need to count the numbers of
the values in different ranges. eg <= $10.00, $10.01 to $100.00,
$100.01 to $500.00, etc.

The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257.

But for the next range, formula
=COUNTIF(H2:H2001,AND("10","<=100")) doesn't work.

What's the problem? How can I get this to work?


TIA

Hi Gordon,

You can try...

=SUMPRODUCT(--(H2:H200110),--(H2:H2001<=100))




"Gordon" wrote in message
oups.com...
I have a list of numbers (2000 lines). I need to count the numbers of
the values in different ranges. eg <= $10.00, $10.01 to $100.00,
$100.01 to $500.00, etc.

The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257.

But for the next range, formula
=COUNTIF(H2:H2001,AND("10","<=100")) doesn't work.

What's the problem? How can I get this to work?


TIA