Sum peak demand for any 7 day period

I have thirty days worth of customer demand for parts. I would like excel to find the greatest sum during any 7 day period and give me that value in a cell at the end of a row of data that includes blank cells and date gaps.

Why make life hard?
See Sheet2

I can't say I'm bouncing up and down with joy at the prospect of finding a SUMPRODUCT() solution to this problem.

Try this workbook Sheet3

This uses only one helper column and two dynamic named ranges.

1/. Select a product from the drop-down in B2 to get the results for each product.

2/. Drag down the dates in Column C. (Note the header changes)
Now add some figures to the column related to the selected part, the data will update in B4 & B6.

3/. Drag the header in Row 2 right, or add new part names/numbers in G2 to whatever , check the change in the drop-down B2.
Now select one of the new options in B2 and add some figures to the matching column.

This has a trade-off, only one part can be displayed at a time, if you want to compare two or three parts it's no big deal to add more helpers and drop-downs.

If you need to see all the data for all parts at once we are probably talking VBa, possibly as UDFs, let me know and I'll give it a go if necessary.
This will almost definitly be easier than a SUMPRODUCT()/COUNTIFS() solution.

A sample of your actual workbook would be an advantage, perhaps for producing a VBa conversion, or a formula based results sheet.

Let me know what you think.

Well, it took me a while, but I think I may have something you can use...

Using your posted workbook...
1) I couldn't get my formulas to work with your Julian dates, so I converted them to Excel dates in Row_1.
2) Since there are gaps in the dates, but the dates are in ascending order, I'm assuming you want to find totals for 7 consecutive dates...not necessarily consecutive cells.
Example: Jan-01, Jan-12
Find the total for Jan-01 through Jan-07

If that's true, this ARRAY-FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER) returns the maximum total for any seven consecutive days.

Please Login or Register  to view this content.

Copy that formula and paste into FD4:FD936

I've attached an edited version of your workbook with those changes.

Is that something you can work with?

1) I have no idea what to call it
2) I've never seen any other formula do the same thing
3) It took me quite a while to figure out how to build it
4) Just because I, or anybody else for that matter, "invented" a formula...doesn't mean it hasn't been done countless other times, but nobody knows about it.
...That's why I never call a formula "my formula" or "your formula" and I never give or take credit for any formula.
Perhaps...some people might say they saw it here first.

Regardless, I wish I had half of your skills. Thank you very much for the help.

Originally Posted by Sebastion

Regardless, I wish I had half of your skills. Thank you very much for the help.

Thanks for the kind words.

FYI: I found some additional efficiencies for the formula I posted.
They're included in the attached file.

Ron

Side note: I sail in Narragansett Bay most weekends during the summer. We're practically neighbors!