formula to find how many cell have a number between 2 numbers.

[Guest]

I have a column that counts the number of days a person in assigned to my
unit. I am trying to make a formula to count how many people have been in my
unit for 1-30 days, 31-60 days and 61-90 days.

 

[Ron Coderre]

Here are a few options

Example:
With this list in A1:B9
Name Days
Alpha 15
Bravo 22
Charlie 29
Delta 36
Echo 43
Foxtrot 50
Golf 57
Hotel 64

and these start/end numbers in D1:E3
1 30
31 60
61 90

Then....for a traditional approach:
OPTION_1
F1: =SUMPRODUCT(($B$2:$B$9>=D1)*($B$2:$B$9<=E1))

OR.....Since your ranges are in tidy groups of 30:
OPTION_2
F1: =SUMPRODUCT(--(CEILING($B$2:$B$9,30)=E1))

OR....for a non-traditional approach:
F1: =INDEX(FREQUENCY($B$2:$B$9,D1:E1-{1,0}),2)

Copy whichever formula you choose down, copy it through F3

Using my posted example:
F1 returns 3
F2 returns 4
F3 returns 1

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

One more common approach...(still using the example I posted):

F1: =COUNTIF($B$2:$B$9,"<="&E1)-COUNTIF($B$2:$B$9,"<"&D1)

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Alan Beban]

I have a column that counts the number of days a person in assigned to my
unit. I am trying to make a formula to count how many people have been in my
unit for 1-30 days, 31-60 days and 61-90 days.

Three versions of a function to do this were recently posted in this
forum; watch for wordwrap:

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
With Application
CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _
.CountIf(iRange, ">" & String(-Not (inclHi), "=") & hiNum)
End With
End Function

Rick Rothstein

Public Function CountBetw( _
iRange As Range, _
lowNum As Double, _
hiNum As Double, _
Optional inclLow = True, _
Optional inclHi = True) As Variant
Dim sOpLow As String
Dim sOpHi As String
sOpLow = IIf(inclLow, ">=", ">")
sOpHi = IIf(inclHi, ">", ">=")
With Application
CountBetw = .CountIf(iRange, sOpLow & lowNum) - _
.CountIf(iRange, sOpHi & hiNum)
End With
End Function

JE McGimpsey

Function CountBetw(iRange As range, lowNum, hiNum, Optional inclLow =
True, Optional inclHi = True)
If inclLow = True And inclHi = True Then
CountBetw = Application.CountIf(iRange, ">=" & lowNum) -
Application.CountIf(iRange, ">" & hiNum)
ElseIf inclLow = False And inclHi = False Then
CountBetw = Application.CountIf(iRange, ">" & lowNum) -
Application.CountIf(iRange, ">=" & hiNum)
ElseIf inclLow = True And inclHi = False Then
CountBetw = Application.CountIf(iRange, ">=" & lowNum) -
Application.CountIf(iRange, ">=" & hiNum)
ElseIf inclLow = False And inclHi = True Then
CountBetw = Application.CountIf(iRange, ">" & lowNum) -
Application.CountIf(iRange, ">" & hiNum)
End If
End Function

Alan Beban