Count with multiple condition

  • Thread starter Thread starter ou
  • Start date Start date
O

ou

Hi all
I want to get the count of rows from A1:A20 where the value is greater
than 10 and smaller than 20. I know I could do it by
=countif(A1:A20, "<20") - countif(A1:A20, "<=10")
But I want to do it within one function (nested functions are ok with me
like f(g,h)) like:
=countif(A1:A20, "<20 and >10")
I've tried
=countif(A1:A20, and("<20", ">10"))
and it doesn't work, a little search on the google turns out that
countif can only have one simple condition within it. And it says that I
should use something called array function to do it. I don't really
understand it. Could any of you help me?

Thanks
Ou

P.S
I know I could use a macro to do the job, but I want the simplest one.
 
Do you mean something like this:

=SUMPRODUCT((A1:A20>10)*(A1:A20<20))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi all
I want to get the count of rows from A1:A20 where the value is greater
than 10 and smaller than 20. I know I could do it by
=countif(A1:A20, "<20") - countif(A1:A20, "<=10")
But I want to do it within one function (nested functions are ok with me
like f(g,h)) like:
=countif(A1:A20, "<20 and >10")
I've tried
=countif(A1:A20, and("<20", ">10"))
and it doesn't work, a little search on the google turns out that
countif can only have one simple condition within it. And it says that I
should use something called array function to do it. I don't really
understand it. Could any of you help me?

Thanks
Ou

P.S
I know I could use a macro to do the job, but I want the simplest one.
 
It seems to be working nice, but I don't understand the syntaxis of the
arguments in your sumproduct function.
could you please elaborate. The help file on sumproduct doesnot suggest any
of your brilliant idea.
 
This has a very good explanation.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

It seems to be working nice, but I don't understand the syntaxis of the
arguments in your sumproduct function.
could you please elaborate. The help file on sumproduct doesnot suggest any
of your brilliant idea.
 
Back
Top