Looking for a rolling max count of item usage

I have included an example of what I am hoping for.
I am trying to get a maximum number of items used in a rolling(hope that term works) time period.
(Meaning that Jan 1, 2013-Feb 14, 2013 would be one example of a 45 day period, Jan 2, 2013-Feb 15, 2013 would be another... etc..)
The item period's length would be specified in 'Item List'! D1
with the results from each sheet displayed in 'Item List'! C4:E4

This is 1 item's usage out of a 3500 item list. I am hoping to use the result of this on all 3500 items.

Thanks for any help you can give,
Mike

That is the problem. I would like to know the highest total from ALL possible 45(or whatever period length chosen) day periods. I don't want to make 1050 (365*3 - 45)separate countifs to account for each possible 45 day period in each 3 year sheet.

largest # I can find (manual counting) is rows 35-49 with a total count of 48

Mike

I could not figure it out , so I decided to cheat , and write a custom function.

a couple of things you need to know.
file must be saved as an .xlxm
dates must be sorted smallest to largest on sheets

copy the code below, open vba alt & F11.
click insert then module
paste the code into it

the name of the function is rollingcount and is under userdefined functions.

I attached the file that i was working with. if you click on the cell with the formula and then Fx you can see the required arguments.

i hope this helps, and if you need anything else just let me know.

Option Explicit

Function RollingCount(WorksheetName As String, Interval As Long, Criteria As String, CriteriaCol As Long, Optional FirstDate As Range) As Long

    Application.Volatile
    
    Dim WsN As Worksheet
    
    Dim lFinalRow As Long
    Dim i As Long
    Dim lTempCount As Long
    Dim lMaxCount As Long
    
    Dim dStartDate As Date
    Dim dEndDAte As Date

    Set WsN = ThisWorkbook.Worksheets(WorksheetName)
    lFinalRow = WsN.Cells(Rows.Count, 1).End(xlUp).Row

    dStartDate = CDate("1/1/" & Year(WsN.Range("A4")))
    dEndDAte = dStartDate + Interval
        
    lTempCount = 0
    lMaxCount = 0
    RollingCount = 0
    
    For i = 4 To lFinalRow
    
        Select Case WsN.Cells(i, 1).Value
            
            Case dStartDate To dEndDAte
                If (StrComp(Trim(Criteria), Trim(WsN.Cells(i, CriteriaCol).Value), vbTextCompare) = 0) Then
                    lTempCount = lTempCount + 1
                End If
            
            Case Else
                i = i - 1
                If lTempCount > lMaxCount Then
                    lMaxCount = lTempCount
                    lTempCount = 0
                End If
                dStartDate = dEndDAte + 1
                dEndDAte = dStartDate + Interval
                
        End Select

    Next i
    If lTempCount > lMaxCount Then lMaxCount = lTempCount
    RollingCount = lMaxCount
End Function

I will try that.

Thanks for helping me

Mike

For some reason, on your attached file, the total on the "2010-2012" sheet for 45 days says 44, when it should be 116.
I have been playing with this for a little bit, and don't understand where the total of 44 is coming from
I changed all the entries to a QTY of 1,and then tried a 2 in every entry, and the total that the formula spits out (in cell D10) doesn't change from 44.


Mike