| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
=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 |
|
#4
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use IF function for two conditions | Excel Worksheet Functions | |||
| IF Function or Countif | Excel Worksheet Functions | |||
| How do you ignore hidden rows in a countif() function | Excel Worksheet Functions | |||
| COUNTIF and then SUM in Same Function | Excel Worksheet Functions | |||
| hOW TO USE THE AND OPERATOR WITH THE COUNTIF FUNCTION | Excel Worksheet Functions | |||
Linear Mode
