Countif numbers fall between two values

[Chris]

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%.

100 are in with the values from >85 and subtracting them gives me a negative

number.

Any ideas?

 

[Mike H]

Try this

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

Mike


Mike

 

[Bernie Deitrick]

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

 

[John C]

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

Hope this helps.

 

[Teethless mama]

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

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