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.

Please Login or Register  to view this content.

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

Originally Posted by Befuddled

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.

I apologize the function was counting the number of times the TTMA# occurred during a time period.

please replace the code with the one below:

Important: Dates need to be sorted ascending, and the first date needs to be in cell "A4". If this is an issue please let me know. I can change.

Please Login or Register  to view this content.