Countif's With Multiple Criteria

[David]

If I have a list of numbers such as:
72
85
75
92
73
83
87
75
79
and I would like to write a function that tells me how
many values are between 75 and 80, how can I do that?
I thought =countif(a1:a50,and("<80",">=75")) would work
but does not. Thanks!!!

 

[Dan E]

David,

Use 2 countif's
=COUNTIF(A1:A50,">=75")-COUNTIF(A1:A50,"<=80")

or a sumproduct
=SUMPRODUCT((A1:A50>=75)*(A1:A50<80))

Dan E

 

[Alan Beban]

Or =COUNTBETWEEN(A1:A50,75,80,,FALSE) where COUNTBETWEEN is

Function COUNTBETWEEN(rng, valLow, valHigh, _
Optional inclLow As Boolean = True, _
Optional inclHigh As Boolean = True)
Select Case inclLow & inclHigh
Case "TrueTrue"
COUNTBETWEEN = Application.CountIf(rng, ">=" & valLow) _
- Application.CountIf(rng, ">" & valHigh)
Case "FalseFalse"
COUNTBETWEEN = Application.CountIf(rng, ">" & valLow) _
- Application.CountIf(rng, ">=" & valHigh)
Case "FalseTrue"
COUNTBETWEEN = Application.CountIf(rng, ">" & valLow) _
- Application.CountIf(rng, ">" & valHigh)
Case "TrueFalse"
COUNTBETWEEN = Application.CountIf(rng, ">=" & valLow) _
- Application.CountIf(rng, ">=" & valHigh)
End Select
End Function

Alan Beban

 

[Ron Rosenfeld]

David,

Use 2 countif's
=COUNTIF(A1:A50,">=75")-COUNTIF(A1:A50,"<=80")

What did I miss?

I would have thought:

=COUNTIF(A1:A50,">=75")-COUNTIF(A1:A50,">80")

with minor variations depending on whether 75 and 80 should be included.


--ron

 

[Dan E]

I don't know what you missed???

I just assumed since the OP put
=countif(a1:a50,and("<80",">=75"))
that he was trying to count all greater than and equal to 75
and less than 80. In order to get that with two countifs you
need to count the number greater than and equal to 75
COUNTIF(A1:A50,">=75") and subtract the number greater
than and equal to 80 (so that 80 isn't included in the total)
hence...

 

[Dan E]

Please disregard that last post, it got away on me . . .

My mistake your right it should have been

=COUNTIF(A1:A50,">=75")-COUNTIF(A1:A50,">=80")

Dan E