YTD Calculations using SumProducts & Offset

sgibbs183

New Member
Joined
Aug 5, 2020
Messages
28
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello,

I have a range of data where I am pulling the actuals & budget for each period from another tab.

Highlighted cell formula
=SUMPRODUCT('Project Data Input'!$E$35:$BG$50,('Project Data Input'!$C$35:$C$50="Budget 23/24")*('Project Data Input'!$D$35:$D$50=$B87)*('Project Data Input'!$E$34:$BG$34=I$86))

1694415615487.png


Then below this I am calculating the YTD of the above
Highlighted cell formula

=SUM(OFFSET(I87,0,0,1,-MONTH(I$111)):I87)

1694415668326.png



What I want to do is just have one table where is is calculating the YTD calculations from the other tab I have - so would be a sumproduct using the offset but I am unsure how to do this.

Please can you show me how I could do this?

Thanks
Sarah
 
I'm not really sure what you mean. The second of my updated formulas in Post 9 does aggregate the monthly totals calculated in the first section (the first formula in post 9). I've been trying to post two xl2bb's and and an image showing the sum of all of FY23 top section equals the bottom left total of the second section. AND it is also validated by the excel status bar sum of those cells when the first section is selected. I'll try to post an image of that as well. But first are the xl2bb miniworksheets, the first is the calculated section, and the second is the data section.

Cell Formulas
RangeFormula
J87:T87,J112:T112J87=EDATE(I87,1)
I88:T91I88=SUMPRODUCT(IFERROR(((--($D$134:$BF$134=I$87))*(--($A$86=$B$135:$B$150))*(--($B88=$C$135:$C$150))*($D$135:$BF$150)),0))
I92:T92,C117:T117I92=SUM(I88:I91)
I113:T116I113=SUMPRODUCT(IFERROR((EDATE(I$112,-(COLUMN(I$112)-COLUMN($I$112)))<=$D$134:$BF$134)*(I$112>=$D$134:$BF$134)*(--($A$111=$B$135:$B$150))*($B88=$C$135:$C$150)*($D$135:$BF$150),0))







Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
134Project NameCategorySpend Type2022/Apr2022/May2022/Jun2022/Jul2022/Aug2022/Sep2022/Oct2022/Nov2022/Dec2023/Jan2023/Feb2023/MarFY222023/Apr2023/May2023/Jun2023/Jul2023/Aug2023/Sep2023/Oct2023/Nov2023/Dec2024/Jan2024/Feb2024/MarFY23YTDYTG2024/Apr2024/May2024/Jun2024/Jul2024/Aug2024/Sep2024/Oct2024/Nov2024/Dec2025/Jan2025/Feb2025/MarFY242025/Apr2025/May2025/Jun2025/Jul2025/Aug2025/Sep2025/Oct2025/Nov2025/Dec2026/Jan2026/Feb2026/MarFY25All Periods
135PED Replacement DevicesApproved BudgetExceptional0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
136PED Replacement DevicesApproved BudgetCapex0.000.000.000.000.000.000.000.000.000.000.000.000.0029,508.000.000.0010,030,000.000.000.000.000.000.000.000.000.0010,059,508.0010,059,508.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.0010,059,508.00
137PED Replacement DevicesApproved BudgetClient Funded0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
138PED Replacement DevicesApproved BudgetOpex0.000.000.000.000.000.000.000.000.000.000.000.000.00102,550.000.000.0022,000.000.000.000.000.000.000.000.000.00124,550.00124,550.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00124,550.00
139PED Replacement DevicesActualExceptional0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
140PED Replacement DevicesActualCapex0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.003,960.0022.4015,852.410.000.000.000.000.000.000.0019,834.813,982.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.0019,834.81
141PED Replacement DevicesActualClient Funded0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
142PED Replacement DevicesActualOpex0.000.000.000.000.000.000.000.000.000.000.000.000.0038,965.9013,601.4615,133.4825,164.0011,476.800.000.000.000.000.000.000.00104,341.6492,865.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00104,341.64
143PED Replacement DevicesBudget 23/24Exceptional0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
144PED Replacement DevicesBudget 23/24Capex0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.0032,966.001,042,392.001,077,581.001,118,913.001,105,484.001,077,336.001,025,000.00786,328.00794,253.00774,271.008,834,524.001,075,358.006,681,585.00309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.25309,853.253,718,239.000.000.000.000.000.000.000.000.000.000.000.000.000.0012,552,763.00
145PED Replacement DevicesBudget 23/24Client Funded0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
146PED Replacement DevicesBudget 23/24Opex0.000.000.000.000.000.000.000.000.000.000.000.000.0042,438.0017,863.0019,063.0023,188.000.000.000.000.000.000.000.000.00102,552.00102,552.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00102,552.00
147PED Replacement DevicesCurrent ForecastExceptional0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
148PED Replacement DevicesCurrent ForecastCapex0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.003,960.0022.4070,603.00148,750.0070,371.004,622,303.00169,426.00528,434.00514,095.00451,713.006,579,677.403,982.006,505,092.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.00290,307.003,483,684.000.000.000.000.000.000.000.000.000.000.000.000.000.0010,063,361.40
149PED Replacement DevicesCurrent ForecastClient Funded0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
150PED Replacement DevicesCurrent ForecastOpex0.000.000.000.000.000.000.000.000.000.000.000.000.0038,965.9013,601.4615,133.4825,164.007,710.002,600.002,600.000.000.000.000.000.00105,774.8492,865.005,200.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00105,774.84
sgibbs
Cell Formulas
RangeFormula
E134:O134,AT134:BD134,AG134:AQ134,R134:AB134E134=EDATE(D134,1)
 

Attachments

  • sgibbs screenshot.png
    sgibbs screenshot.png
    57.1 KB · Views: 4
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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