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?

Countif numbers fall between two values
 

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?

Countif numbers fall between two values
 

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" wrote in message
...
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?

Countif numbers fall between two values
 

=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?

Countif numbers fall between two values
 

=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?