How do I Countif for a range of values

[iMartyn]

I want to check a range of numeric values and count the number that are
between 5 and 10 say. I've tried "=COUNTIF(A1:A30, ">5 and <10") but
doesn't seem to work.
Can this be done ?

PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and
then sum separately, but looking for a neater way to do it.

Thanks
iMartyn

[bpeltzer]

If it's a continuous range so as [5,10), you can get there by counting those
five or greater and subtracting the count of those greater than or equal to
10:
=countif(a1:a30,">=5")-countif(a1:a30,">=10")

"iMartyn" wrote:

> I want to check a range of numeric values and count the number that are
> between 5 and 10 say. I've tried "=COUNTIF(A1:A30, ">5 and <10") but
> doesn't seem to work.
> Can this be done ?
>
> PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and
> then sum separately, but looking for a neater way to do it.
>
> Thanks
> iMartyn

[Dave Peterson]

One way:
=COUNTIF(A1:A30,"<10")-COUNTIF(A1:A30,"<=5")
and another:
=SUMPRODUCT(--(A1:A30>5),--(A1:A30<10))

=sumproduct() likes to work with numbers. The -- stuff changes trues to 1's and
falses to 0's.



iMartyn wrote:
>
> I want to check a range of numeric values and count the number that are
> between 5 and 10 say. I've tried "=COUNTIF(A1:A30, ">5 and <10") but
> doesn't seem to work.
> Can this be done ?
>
> PS -I'm aware I could count each single value (eg 5, 6, 7, 8, 9, 10) and
> then sum separately, but looking for a neater way to do it.
>
> Thanks
> iMartyn

--

Dave Peterson

[iMartyn]

Elegant ! Thanks very much.

"bpeltzer" wrote:

> If it's a continuous range so as [5,10), you can get there by counting those
> five or greater and subtracting the count of those greater than or equal to
> 10:
> =countif(a1:a30,">=5")-countif(a1:a30,">=10")
>

[iMartyn]

Wow- That's really A-Level. Thanks.

"Dave Peterson" wrote:

> One way:
> =COUNTIF(A1:A30,"<10")-COUNTIF(A1:A30,"<=5")
> and another:
> =SUMPRODUCT(--(A1:A30>5),--(A1:A30<10))
>
> =sumproduct() likes to work with numbers. The -- stuff changes trues to 1's and
> falses to 0's.