Scan for max sum of 3 concurrent days

[RobotCarl]

Greetings all,

I am attempting to find the maximum sum of xx amount of concurrent days within the month. In the below example if my desired range is 3 days I would like to know the max. Looking at 3 days out of the 8, the sum of days 3,4,5 would be the max sum for three concurrent days. I would like Max Sum for desired day range to = 6. Anyone know how to achieve this?

Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day7	Day 8	Desired Day Range *Criteria	Max Sum for 3 Days
1	0	2	2	2	0	1	4	3

 

[jasonb75]

A couple of options for you depending on which version of excel you are using.

DavyJones90.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Criteria	Max sum
2	1	0	2	2	2	0	1	4	3	6	<< Excel 2019 or older
3										6	<< Excel 2021 or newer
Sheet2

Cell Formulas
Range	Formula
J2	J2	=MAX(SUBTOTAL(9,OFFSET($A$2,,COLUMN($A$2:OFFSET($A$2,,COLUMN($I$2)-1-$I$2))-COLUMN($A$2),,3)))
J3	J3	=MAX(SUBTOTAL(9,OFFSET($A$2,,SEQUENCE(,COLUMNS($A$2:$H$2)-$I$2,0),,3)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RobotCarl]

Thank you! I knew my efforts were on the cusp but you helped me out tremendously!