Counting cells between two values

The following function, named CountBetween , returns the number of values in a range (first argument) that fall between values represented by the second and third arguments:

Function CountBetween(InRange, num1, num2) As Long ' Counts number of values between num1 and num2 With Application.WorksheetFunction If num1 <= num2 Then

CountBetween = .CountIf(InRange, ">=" & .CountIf(InRange, ">" & num2)

Else

CountBetween = .CountIf(InRange, ">=" & .CountIf(InRange, ">" & num1)

End If End With End Function

Note that this function uses Excel's COUNTIF function. In fact, the CountBetween function is essentially a wrapper that can simplify your formulas.

Following is an example formula that uses the CountBetween function. The formula returns the number of cells in A1:A100 that are greater than or equal to 10 and less than or equal to 20.

=CountBetween(A1:A10 0,10,20)

Using this VBA function is simpler than entering the following lengthy (and somewhat confusing) formula:

=COUNTIF(A1:A100,">=10")-COUNTIF(A1:A100,">20")

0 0

Post a comment