YTD Calculations using SumProducts & Offset

[sgibbs183]

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))

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

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

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

 

[awoohaw]

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.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
85	Budget/Spend
86	Budget 23/24	Budget
87	Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	2023/Apr	2023/May	2023/Jun	2023/Jul	2023/Aug	2023/Sep	2023/Oct	2023/Nov	2023/Dec	2024/Jan	2024/Feb	2024/Mar	FY23	FY24	FY25	FY26	FY27	Total
88		Capex							0.00	0.00	32,966.00	1,042,392.00	1,077,581.00	1,118,913.00	1,105,484.00	1,077,336.00	1,025,000.00	786,328.00	794,253.00	774,271.00
89		Opex							42,438.00	17,863.00	19,063.00	23,188.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
90		Exceptional							0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
91		Client Funded							0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
92	TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	42,438.00	17,863.00	52,029.00	1,065,580.00	1,077,581.00	1,118,913.00	1,105,484.00	1,077,336.00	1,025,000.00	786,328.00	794,253.00	774,271.00
93
94
108
109
110	YTD Budget/Spend								YTD
111	Budget 23/24	Budget
112	Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	2023/Apr	2023/May	2023/Jun	2023/Jul	2023/Aug	2023/Sep	2023/Oct	2023/Nov	2023/Dec	2024/Jan	2024/Feb	2024/Mar	FY23	FY24	FY25	FY26	FY27	Total
113									0.00	0.00	32,966.00	1,075,358.00	2,152,939.00	3,271,852.00	4,377,336.00	5,454,672.00	6,479,672.00	7,266,000.00	8,060,253.00	8,834,524.00
114									42,438.00	60,301.00	79,364.00	102,552.00	102,552.00	102,552.00	102,552.00	102,552.00	102,552.00	102,552.00	102,552.00	102,552.00
115									0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
116									0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
117	TOTAL		0.00	0.00	0.00	0.00	0.00	0.00	42,438.00	60,301.00	112,330.00	1,177,910.00	2,255,491.00	3,374,404.00	4,479,888.00	5,557,224.00	6,582,224.00	7,368,552.00	8,162,805.00	8,937,076.00
sgibbs

Cell Formulas
Range	Formula
J87:T87,J112:T112	J87	=EDATE(I87,1)
I88:T91	I88	=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:T117	I92	=SUM(I88:I91)
I113:T116	I113	=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
134	Project Name	Category	Spend Type	2022/Apr	2022/May	2022/Jun	2022/Jul	2022/Aug	2022/Sep	2022/Oct	2022/Nov	2022/Dec	2023/Jan	2023/Feb	2023/Mar	FY22	2023/Apr	2023/May	2023/Jun	2023/Jul	2023/Aug	2023/Sep	2023/Oct	2023/Nov	2023/Dec	2024/Jan	2024/Feb	2024/Mar	FY23	YTD	YTG	2024/Apr	2024/May	2024/Jun	2024/Jul	2024/Aug	2024/Sep	2024/Oct	2024/Nov	2024/Dec	2025/Jan	2025/Feb	2025/Mar	FY24	2025/Apr	2025/May	2025/Jun	2025/Jul	2025/Aug	2025/Sep	2025/Oct	2025/Nov	2025/Dec	2026/Jan	2026/Feb	2026/Mar	FY25	All Periods
135	PED Replacement Devices	Approved Budget	Exceptional	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
136	PED Replacement Devices	Approved Budget	Capex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	29,508.00	0.00	0.00	10,030,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,059,508.00	10,059,508.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,059,508.00
137	PED Replacement Devices	Approved Budget	Client Funded	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
138	PED Replacement Devices	Approved Budget	Opex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	102,550.00	0.00	0.00	22,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	124,550.00	124,550.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	124,550.00
139	PED Replacement Devices	Actual	Exceptional	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
140	PED Replacement Devices	Actual	Capex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3,960.00	22.40	15,852.41	0.00	0.00	0.00	0.00	0.00	0.00	0.00	19,834.81	3,982.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	19,834.81
141	PED Replacement Devices	Actual	Client Funded	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
142	PED Replacement Devices	Actual	Opex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	38,965.90	13,601.46	15,133.48	25,164.00	11,476.80	0.00	0.00	0.00	0.00	0.00	0.00	0.00	104,341.64	92,865.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	104,341.64
143	PED Replacement Devices	Budget 23/24	Exceptional	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
144	PED Replacement Devices	Budget 23/24	Capex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	32,966.00	1,042,392.00	1,077,581.00	1,118,913.00	1,105,484.00	1,077,336.00	1,025,000.00	786,328.00	794,253.00	774,271.00	8,834,524.00	1,075,358.00	6,681,585.00	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	309,853.25	3,718,239.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	12,552,763.00
145	PED Replacement Devices	Budget 23/24	Client Funded	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
146	PED Replacement Devices	Budget 23/24	Opex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	42,438.00	17,863.00	19,063.00	23,188.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	102,552.00	102,552.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	102,552.00
147	PED Replacement Devices	Current Forecast	Exceptional	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
148	PED Replacement Devices	Current Forecast	Capex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3,960.00	22.40	70,603.00	148,750.00	70,371.00	4,622,303.00	169,426.00	528,434.00	514,095.00	451,713.00	6,579,677.40	3,982.00	6,505,092.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	290,307.00	3,483,684.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,063,361.40
149	PED Replacement Devices	Current Forecast	Client Funded	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
150	PED Replacement Devices	Current Forecast	Opex	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	38,965.90	13,601.46	15,133.48	25,164.00	7,710.00	2,600.00	2,600.00	0.00	0.00	0.00	0.00	0.00	105,774.84	92,865.00	5,200.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	105,774.84
sgibbs

Cell Formulas
Range	Formula
E134:O134,AT134:BD134,AG134:AQ134,R134:AB134	E134	=EDATE(D134,1)