+ Reply to Thread
Results 1 to 5 of 5

using countif with greater than and less than condition

  1. using countif with greater than and less than condition

    Hi all,

    I am new to excel and i have the following list of number to count

    11
    12
    13
    12
    34
    24
    213
    213123
    2
    3
    4
    5

    I found that it is easy to count the number with single condition, for
    example, counting the number less than 10

    COUNTIF(number_list,">10") gives 4

    However, I could not find the way to enter mulitple conditions, and,
    the following functions give 0,

    COUNTIF(number_list,">10 && <15")
    COUNTIF(number_list,">10 & <15")

    Please enlighten me how to count the nuber with multiple conditions.

    Thanks in advance for you inputs.

    Wing

  2. Re: using countif with greater than and less than condition

    "wing" wrote...
    ....
    >I found that it is easy to count the number with single condition, for
    >example, counting the number less than 10
    >
    >COUNTIF(number_list,">10") gives 4
    >
    >However, I could not find the way to enter mulitple conditions, and,
    >the following functions give 0,
    >
    >COUNTIF(number_list,">10 && <15")
    >COUNTIF(number_list,">10 & <15")

    ....

    COUNTIF (and SUMIF) take a single condition. If you need to apply multiple
    conditions, it's usially best to use SUMPRODUCT.

    =SUMPRODUCT((number_list>10)*(number_list<15))

    But you could also use two COUNTIFs in this case.

    =COUNTIF(number_list,">10")-COUNTIF(number_list,">=15")



  3. Re: using countif with greater than and less than condition

    Hi Harlan,

    Thanks for your input.

    I have tried using COUNTIF and it works
    =COUNTIF(number_list,">10")-COUNTIF(number_list,">=15")

    However, =SUMPRODUCT((number_list>10)*(number_list<15)) gives zero, am
    i mssing anything?

    Thanks,

    Wing

    "Harlan Grove" wrote in message news:...
    > "wing" wrote...
    > ...
    > >I found that it is easy to count the number with single condition, for
    > >example, counting the number less than 10
    > >
    > >COUNTIF(number_list,">10") gives 4
    > >
    > >However, I could not find the way to enter mulitple conditions, and,
    > >the following functions give 0,
    > >
    > >COUNTIF(number_list,">10 && <15")
    > >COUNTIF(number_list,">10 & <15")

    > ...
    >
    > COUNTIF (and SUMIF) take a single condition. If you need to apply multiple
    > conditions, it's usially best to use SUMPRODUCT.
    >
    > =SUMPRODUCT((number_list>10)*(number_list<15))
    >
    > But you could also use two COUNTIFs in this case.
    >
    > =COUNTIF(number_list,">10")-COUNTIF(number_list,">=15")


  4. Re: using countif with greater than and less than condition

    "wing" wrote...
    ....
    >I have tried using COUNTIF and it works
    >=COUNTIF(number_list,">10")-COUNTIF(number_list,">=15")
    >
    >However, =SUMPRODUCT((number_list>10)*(number_list<15)) gives zero, am
    >i mssing anything?

    ....

    If you're using Excel and number_list is a range containing numeric values,
    then both formulas should give the same result. With number_list defined as
    A1:A8 containing {9;10;11;12;13;14;15;16}, the latter formula returns 4 on
    my system running Excel 2000.

    Too many people have successfully used the SUMPRODUCT idiom for me to doubt
    that it works. What's the *exact* formula you entered and how have you
    defined number_list?



  5. Re: using countif with greater than and less than condition

    Hi Harlan,

    Yes, you are right,

    I have spotted the reason. In my first try, I am using the gui
    assistance in excel rather than pasting the formula.

    Thus, I have miss-typed (number_list>10) in array1 and
    (number_list<15) in array 2.

    Thanks for your kindly help.

    Wing


    "Harlan Grove" wrote in message news:...
    > "wing" wrote...
    > ...
    > >I have tried using COUNTIF and it works
    > >=COUNTIF(number_list,">10")-COUNTIF(number_list,">=15")
    > >
    > >However, =SUMPRODUCT((number_list>10)*(number_list<15)) gives zero, am
    > >i mssing anything?

    > ...
    >
    > If you're using Excel and number_list is a range containing numeric values,
    > then both formulas should give the same result. With number_list defined as
    > A1:A8 containing {9;10;11;12;13;14;15;16}, the latter formula returns 4 on
    > my system running Excel 2000.
    >
    > Too many people have successfully used the SUMPRODUCT idiom for me to doubt
    > that it works. What's the *exact* formula you entered and how have you
    > defined number_list?


+ Reply to Thread