Countif Using Multiple Logic Tests

I am trying to use countif to count the number of times a number greater than
3 AND less than 10 occurs in a table. I can use countif for one criteria only
but cannot find a way to get it to work with more than one logic test. The
following formula does not work: COUNTIF(A1:A8,"and(>3,<10)")
Thanks in advance,

Use 2

=COUNTIF(A1:A8,">3")-COUNTIF(A1:A8,">=10")

or sumproduct

=SUMPRODUCT(--(A1:A8>3),--(A1:A8<10))

will return the same result

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Carl" <[email protected]> wrote in message
news:[email protected]...
>I am trying to use countif to count the number of times a number greater
>than
> 3 AND less than 10 occurs in a table. I can use countif for one criteria
> only
> but cannot find a way to get it to work with more than one logic test. The
> following formula does not work: COUNTIF(A1:A8,"and(>3,<10)")
> Thanks in advance,

Try something like this:

=SUMPRODUCT((A1:A8>3)*(A1:A8<10))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Carl" wrote:

> I am trying to use countif to count the number of times a number greater than
> 3 AND less than 10 occurs in a table. I can use countif for one criteria only
> but cannot find a way to get it to work with more than one logic test. The
> following formula does not work: COUNTIF(A1:A8,"and(>3,<10)")
> Thanks in advance,

That solved the problem. Thanks!

"Peo Sjoblom" wrote:

> Use 2
>
> =COUNTIF(A1:A8,">3")-COUNTIF(A1:A8,">=10")
>
> or sumproduct
>
> =SUMPRODUCT(--(A1:A8>3),--(A1:A8<10))
>
> will return the same result
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Carl" <[email protected]> wrote in message
> news:[email protected]...
> >I am trying to use countif to count the number of times a number greater
> >than
> > 3 AND less than 10 occurs in a table. I can use countif for one criteria
> > only
> > but cannot find a way to get it to work with more than one logic test. The
> > following formula does not work: COUNTIF(A1:A8,"and(>3,<10)")
> > Thanks in advance,
>
>
>