Countif numbers fall between two values

Hi,

Looking for a formula that will allow me to count how many numbers in a
range fall between larger than 50% and smaller than 85% as well as larger
than 85% and less than 100%.

Currrently I am using a countif(range,<.5) and countif(range>.5) but if I
want to expand my criteria to count how many are >85 and >100, the values for
>100 are in with the values from >85 and subtracting them gives me a negative
number.

Any ideas?

MikeH
Thu Jul 24 08:10:01 CDT 2008

Try this

=COUNTIF(A1:A96,">50%")-COUNTIF(A1:A96,">85%")
=COUNTIF(A1:A96,">85%")-COUNTIF(A1:A96,">100%")

Mike


Mike


"Chris" wrote:

> Hi,
>
> Looking for a formula that will allow me to count how many numbers in a
> range fall between larger than 50% and smaller than 85% as well as larger
> than 85% and less than 100%.
>
> Currrently I am using a countif(range,<.5) and countif(range>.5) but if I
> want to expand my criteria to count how many are >85 and >100, the values for
> >100 are in with the values from >85 and subtracting them gives me a negative
> number.
>
> Any ideas?

Bernie
Thu Jul 24 08:12:46 CDT 2008

Chris,

Typically, for any band

=countif(range,>lowerlimit)-countif(range,>upperlimit)

OR

=countif(range,<upperlimit)-countif(range,<lowerlimit)


So, for your specific problem, for example:

=COUNTIF(A:A,">85")-COUNTIF(A:A,">100")

Of course, you may need to use >= or <= in one or both of the comparisons, depending on how you want
to handle border issues.

HTH,
Bernie
MS Excel MVP


"Chris" <Chris@discussions.microsoft.com> wrote in message
news:8DDD7993-3820-42FB-9958-203816709127@microsoft.com...
> Hi,
>
> Looking for a formula that will allow me to count how many numbers in a
> range fall between larger than 50% and smaller than 85% as well as larger
> than 85% and less than 100%.
>
> Currrently I am using a countif(range,<.5) and countif(range>.5) but if I
> want to expand my criteria to count how many are >85 and >100, the values for
>>100 are in with the values from >85 and subtracting them gives me a negative
> number.
>
> Any ideas?

johnc
Thu Jul 24 08:14:01 CDT 2008

=SUMPRODUCT(--(A1:A100>.5),--(A1:A100<.85))
=SUMPRODUCT(--(A1:A100>.85),--(A1:A100<1))

Hope this helps.
--
John C


"Chris" wrote:

> Hi,
>
> Looking for a formula that will allow me to count how many numbers in a
> range fall between larger than 50% and smaller than 85% as well as larger
> than 85% and less than 100%.
>
> Currrently I am using a countif(range,<.5) and countif(range>.5) but if I
> want to expand my criteria to count how many are >85 and >100, the values for
> >100 are in with the values from >85 and subtracting them gives me a negative
> number.
>
> Any ideas?

Teethlessmama
Thu Jul 24 08:28:02 CDT 2008

=SUM(COUNTIF(A:A,{">0.5",">=0.85"})*{1,-1})

=SUM(COUNTIF(A:A,{">0.85",">=1"})*{1,-1})


"Chris" wrote:

> Hi,
>
> Looking for a formula that will allow me to count how many numbers in a
> range fall between larger than 50% and smaller than 85% as well as larger
> than 85% and less than 100%.
>
> Currrently I am using a countif(range,<.5) and countif(range>.5) but if I
> want to expand my criteria to count how many are >85 and >100, the values for
> >100 are in with the values from >85 and subtracting them gives me a negative
> number.
>
> Any ideas?