2 worksheets one rolling 12 month sum

Shazzaz

New Member
Joined
Aug 28, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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..........
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Welcome to the Forum!

Perhaps something along these lines?

ABCDEFGHIJKLMN
1Fin Year2025
2
3MonthJul 2024Aug 2024Sep 2024Oct 2024Nov 2024Dec 2024Jan 2025Feb 2025Mar 2025Apr 2025May 2025Jun 2025
4Values414347
5
612 months toAug 2024
7Total values276
8
2025
Cell Formulas
RangeFormula
B3:M3B3=DATE(B1,SEQUENCE(,12,-5),1)
B7B7=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.

ABCDEFGHIJKLMN
1Fin Year2024
2
3MonthJul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024Apr 2024May 2024Jun 2024
4Values23571113171923293137
5
6
2024
Cell Formulas
RangeFormula
B3:M3B3=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.
 
Upvote 0
Brilliant Thank you, I will give it a go really appreciate the response! so helpful!
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,590
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top