This is a very commonly asked question, the standard answers to which
are either a SUMPRODUCT formula or a combination of two COUNTIF
formulas, each of which needs to have the appropriate <, >, <= and >=
symbols, depending on which end values are to be included. The following
is little function that can be used for all four of the possibilities:
Include both endpoints--True,True (or omit both Boolean arguments);
include neither endpoint--False, False; include the lower endpoint but
not the upper--True,False; or omit the lower endpoint but not the
upper--False,True. It is not, of course, as efficient as the
appropriate formula with the appropriate comparison operators for a
particular case, but it might be easier to use than puzzling out the
appropriate comparison operators each time.
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
