Rolling 5 Months Formula on Excel

[monteee]

Hello I have a set of data with columns from Nov-21 to Dec-22 and the data / columns will be updated monthly. I am searching for a formula that allows me to enter new monthly data that will automatically calculate and update a total of 5 months (highlighted in Yellow). For instance, the formula will be able to calculate the sum of skirts from Dec-21 to Apr-22 in May, followed by Jan-22 to May-22 next month. I have tried to use the offset formula, however I couldn't figure out the logic of the formula. Can someone please help me? Thank you in advance for your assistance to this matter! =)

 

[FormR]

Hi, welcome to the forum!

Here is one way that uses the current date to decide which months to include. This assumes the dates in the first row are actual Excel date serial numbers and are the 1st of the month.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Nov-21		Dec-21		Jan-22		Feb-22		Mar-22		Apr-22		May-22		Jun-22		rolling 5 month
2	staff	skirts	tops	skirts	tops	skirts	tops	skirts	tops	skirts	tops	skirts	tops	skirts	tops	skirts	tops	skirts	tops
3	A	28	21	16	39	51	30	49	32	41	32	5	49	46	23			192	166
4	B	3	52	49	30	36	29	22	15	24	51	1	33	40	35			123	163
5	C	21	8	53	14	28	8	40	3	17	5	42	31	28	46			155	93
6	D	53	32	12	46	12	17	47	44	16	20	50	44	28	48			153	173
7	E	4	3	27	16	5	3	35	31	41	51	2	9	26	21			109	115
Sheet1

Cell Formulas
Range	Formula
R3:S7	R3	=SUMIFS(B3:P3,B$2:P$2,R$2,$B$1:$P$1,">="&EOMONTH(TODAY(),-6)+1,$B$1:$P$1,"<"&EOMONTH(TODAY(),-1)+1)

 

[monteee]

Thanks! It works perfectly