Countif's With Multiple Criteria

  • Thread starter Thread starter David
  • Start date Start date
D

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!!!
 
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
 
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
 
David,

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


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
 
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...
 
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
 
Back
Top