
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

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")

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")

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?

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 misstyped (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?