2 worksheets one rolling 12 month sum

[Shazzaz]

I have a workbook where a new duplicate worksheet is created every year, I need a rolling 12 month formula to sum for example July in the current sheet and August through June in the previous years worksheet and so on, in August of current year I need July and August from current worksheet and September through June in the previous worksheet..........

 

[StephenCrump]

Welcome to the Forum!

Perhaps something along these lines?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Fin Year	2025
2
3	Month	Jul 2024	Aug 2024	Sep 2024	Oct 2024	Nov 2024	Dec 2024	Jan 2025	Feb 2025	Mar 2025	Apr 2025	May 2025	Jun 2025
4	Values	41	43	47
5
6	12 months to	Aug 2024
7	Total values	276
8
2025

Cell Formulas
Range	Formula
B3:M3	B3	=DATE(B1,SEQUENCE(,12,-5),1)
B7	B7	=SUM(FILTER(B4:M4,EOMONTH(--B3#,0)<=EOMONTH(B6,0),0),FILTER('2024'!B4:M4,EOMONTH(--'2024'!B3#,0)>EOMONTH(B6,-12),0))
Dynamic array formulas.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Fin Year	2024
2
3	Month	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Jan 2024	Feb 2024	Mar 2024	Apr 2024	May 2024	Jun 2024
4	Values	2	3	5	7	11	13	17	19	23	29	31	37
5
6
2024

Cell Formulas
Range	Formula
B3:M3	B3	=DATE(B1,SEQUENCE(,12,-5),1)
Dynamic array formulas.

You could take it to the next level with:

=SUM(FILTER(B4:M4,EOMONTH(--B3#,0)<=EOMONTH(B6,0),0),FILTER(INDIRECT("'" & B1-1 &"'!B4:M4"),EOMONTH(--INDIRECT("'" & B1-1& "'!B3#"),0)>EOMONTH(B6,-12),0))

as you could copy this formula to the new 2026 work sheet, when it was time, without needing to change any sheet references.

 

[Shazzaz]

Brilliant Thank you, I will give it a go really appreciate the response! so helpful!