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]

HI Sarah,
It would be really helpful if you could use the xl2bb add in (link below) to post miniworksheet portions of your different data tabs as well as the formula sections you have. I will take a forum member quite a while to recreate your scenario.

If you cannot use the xl2bb add in the please post the sample data in tables and the formulas in text (please identify column rows).

 

[sgibbs183]

It is the formulas in the green highlighted cells which I would like to combine, so I am doing a sumproduct with the offset function...

MREXcel example.xlsx
	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	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
88		Capex	-	-	-	-	-	-	-	-	32,966	1,042,392	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,834,524	3,718,239	-	-	-	12,552,763
89		Opex	-	-	-	-	-	-	42,438	17,863	19,063	23,188	-	-	-	-	-	-	-	-	102,552	-	-	-	-	102,552
90		Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
91		Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
92	TOTAL		-	-	-	-	-	-	42,438	17,863	52,029	1,065,580	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,937,076	3,718,239	-	-	-	12,655,315
109
110	YTD Budget/Spend								YTD
111	Budget 23/24	Budget
112	Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
113	Waterfall PED Replacement Devices	Capex	-	-	-	-	-	-	-	-	32,966	1,075,358	2,152,939	3,271,852	4,377,336	5,454,672	6,479,672	786,328	1,580,581	2,354,852	8,834,524	3,718,239	-	-	-	12,552,763
114	Waterfall PED Replacement Devices	Opex	-	-	-	-	-	-	42,438	60,301	79,364	102,552	102,552	102,552	102,552	102,552	102,552	-	-	-	102,552	-	-	-	-	102,552
115	Waterfall PED Replacement Devices	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
116	Waterfall PED Replacement Devices	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
117	TOTAL		-	-	-	-	-	-	42,438	60,301	112,330	1,177,910	2,255,491	3,374,404	4,479,888	5,557,224	6,582,224	786,328	1,580,581	2,354,852	8,937,076	3,718,239	-	-	-	12,655,315
Overview

Cell Formulas
Range	Formula
C88:Y91,C113:H116,U113:Y116	C88	=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=$B88)*('Project Data Input'!$E$34:$BG$34=C$87))
I113:T116	I113	=SUM(OFFSET(I88,0,0,1,-MONTH(I$112)):I88)
Z113:Z115	Z113	=C113+D113+E113+F113+G113+H113+U113+V113+W113+X113+Y113
A113:A116	A113	='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$B$9
C117:Z117	C117	=SUM(C113:C116)

 

[sgibbs183]

It is the formulas in the green highlighted cells which I would like to combine, so I am doing a sumproduct with the offset function...

MREXcel example.xlsx
	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	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
88		Capex	-	-	-	-	-	-	-	-	32,966	1,042,392	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,834,524	3,718,239	-	-	-	12,552,763
89		Opex	-	-	-	-	-	-	42,438	17,863	19,063	23,188	-	-	-	-	-	-	-	-	102,552	-	-	-	-	102,552
90		Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
91		Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
92	TOTAL		-	-	-	-	-	-	42,438	17,863	52,029	1,065,580	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,937,076	3,718,239	-	-	-	12,655,315
109
110	YTD Budget/Spend								YTD
111	Budget 23/24	Budget
112	Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
113	Waterfall PED Replacement Devices	Capex	-	-	-	-	-	-	-	-	32,966	1,075,358	2,152,939	3,271,852	4,377,336	5,454,672	6,479,672	786,328	1,580,581	2,354,852	8,834,524	3,718,239	-	-	-	12,552,763
114	Waterfall PED Replacement Devices	Opex	-	-	-	-	-	-	42,438	60,301	79,364	102,552	102,552	102,552	102,552	102,552	102,552	-	-	-	102,552	-	-	-	-	102,552
115	Waterfall PED Replacement Devices	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
116	Waterfall PED Replacement Devices	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
117	TOTAL		-	-	-	-	-	-	42,438	60,301	112,330	1,177,910	2,255,491	3,374,404	4,479,888	5,557,224	6,582,224	786,328	1,580,581	2,354,852	8,937,076	3,718,239	-	-	-	12,655,315
Overview

Cell Formulas
Range	Formula
C88:Y91,C113:H116,U113:Y116	C88	=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=$B88)*('Project Data Input'!$E$34:$BG$34=C$87))
I113:T116	I113	=SUM(OFFSET(I88,0,0,1,-MONTH(I$112)):I88)
Z113:Z115	Z113	=C113+D113+E113+F113+G113+H113+U113+V113+W113+X113+Y113
A113:A116	A113	='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$B$9
C117:Z117	C117	=SUM(C113:C116)

Also I have noticed that the offset function is only working for the months not for the financial year, for example this calculation doesn't calculate teh YTD total for Jan 24

 

[awoohaw]

Thanks. But, can you also post some of the data (anonymized) on the worksheet "Project Data Input"?

 

[sgibbs183]

Project Name	Category	Spend Type	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Feb 23	Mar 23	FY22	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	YTD	YTG	Apr 24	May 24	Jun 24	Jul 24	Aug 24	Sep 24	Oct 24	Nov 24	Dec 24	Jan 25	Feb 25	Mar 25	FY24	Apr 25	May 25	Jun 25	Jul 25	Aug 25	Sep 25	Oct 25	Nov 25	Dec 25	Jan 26	Feb 26	Mar 26	FY25	All Periods
PED Replacement Devices	Approved Budget	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Approved Budget	Capex	-	-	-	-	-	-	-	-	-	-	-	-	-	29,508	-	-	10,030,000	-	-	-	-	-	-	-	-	10,059,508	10,059,508	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	10,059,508
PED Replacement Devices	Approved Budget	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Approved Budget	Opex	-	-	-	-	-	-	-	-	-	-	-	-	-	102,550	-	-	22,000	-	-	-	-	-	-	-	-	124,550	124,550	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	124,550
PED Replacement Devices	Actual	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Actual	Capex	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	3,960	22	15,852	-	-	-	-	-	-	-	19,835	3,982	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	19,835
PED Replacement Devices	Actual	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Actual	Opex	-	-	-	-	-	-	-	-	-	-	-	-	-	38,966	13,601	15,133	25,164	11,477	-	-	-	-	-	-	-	104,342	92,865	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	104,342
PED Replacement Devices	Budget 23/24	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Budget 23/24	Capex	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	32,966	1,042,392	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,834,524	1,075,358	6,681,585	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	3,718,239	-	-	-	-	-	-	-	-	-	-	-	-	-	12,552,763
PED Replacement Devices	Budget 23/24	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
PED Replacement Devices	Budget 23/24	Opex	-	-	-	-	-	-	-	-	-	-	-	-	-	42,438	17,863	19,063	23,188	-	-	-	-	-	-	-	-	102,552	102,552	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	102,552
PED Replacement Devices	Current Forecast	Exceptional	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	-	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
PED Replacement Devices	Current Forecast	Capex	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3,960	22	70,603	148,750	70,371	4,622,303	169,426	528,434	514,095	451,713	6,579,677	3,982	6,505,092	290,307	290,307	290,307	290,307	290,307	290,307	290,307	290,307	290,307	290,307	290,307	290,307	3,483,684	0	0	0	0	0	0	0	0	0	0	0	0	0	10,063,361
PED Replacement Devices	Current Forecast	Client Funded	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	-	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
PED Replacement Devices	Current Forecast	Opex	0	0	0	0	0	0	0	0	0	0	0	0	0	38,966	13,601	15,133	25,164	7,710	2,600	2,600	0	0	0	0	0	105,775	92,865	5,200	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	105,775

 

[sgibbs183]

Hello, do you have an answer for this please?

 

[awoohaw]

I am not a fan of OFFSET function as it is volatile and can slow down worksheet performance. Not sure how many times you would be using it here if it would make a difference. This is using the COLUMN function to get the number of months to look back. This can probably be done more eloquently with 365 LET and FILTER functions, but it seems to work here.

Also, I'm not sure if it is my settings or just the way I pasted your data, but i had the dates messed up and had to guess how they were supposed to be. I hope I guessed correctly (i don't think it is your fault in any way). I've also consolidated the two worksheets into one. I am pasting two xl2bb mini sheets but they are in the same worksheet in my workbook.

I hope this works for you.

Best Wishes!

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	0	0	0	0	0	29,508.00	0.00	40,886.00	11,072,436.80	1,164,036.41	1,267,663.00	1,175,855.00	5,699,639.00	1,194,426.00	1,314,762.00	1,308,348.00	1,225,984.00
89		Opex	0	0	0	0	0	0	222,919.80	45,065.92	49,329.96	95,516.00	19,186.80	2,600.00	2,600.00	0.00	0.00	0.00	0.00	0.00
90		Exceptional	0	0	0	0	0	0	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	0	0	0	0	0	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	0	0	0	0	0	252,427.80	45,065.92	90,215.96	11,167,952.80	1,183,223.21	1,270,263.00	1,178,455.00	5,699,639.00	1,194,426.00	1,314,762.00	1,308,348.00	1,225,984.00
93
94
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	Actual	Capex	0	0	0	0	0	0	29,508.00	29,508.00	70,394.00	11,142,830.80	12,306,867.21	13,574,530.21	14,750,385.21	20,450,024.21	21,644,450.21	22,959,212.21	24,267,560.21	25,493,544.21
114	Actual	Opex	0	0	0	0	0	0	222,919.80	267,985.72	317,315.68	412,831.68	432,018.48	434,618.48	437,218.48	437,218.48	437,218.48	437,218.48	437,218.48	437,218.48
115	Actual	Exceptional	0	0	0	0	0	0	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	Actual	Client Funded	0	0	0	0	0	0	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	0	0	0	0	0	252,427.80	297,493.72	387,709.68	11,555,662.48	12,738,885.69	14,009,148.69	15,187,603.69	20,887,242.69	22,081,668.69	23,396,430.69	24,704,778.69	25,930,762.69
Overview

Cell Formulas
Range	Formula
J87:T87,J112:T112	J87	=EDATE(I87,1)
C88:H91,C113:H116	C88	=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=$B88)*('Project Data Input'!$E$34:$BG$34=C$87))
I88:T91	I88	=SUMPRODUCT(IFERROR(((--($D$134:$BF$134=I$87))*(--($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)*($B88=$C$135:$C$150)*($D$135:$BF$150),0))
A113:A116	A113	='Project Data Input'!$B$9

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	BG
133
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
151
Overview

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

 

[awoohaw]

Sorry for posting so quickly after the prior post. But I failed to add an additional filter you wanted in the formulas.
Here are updated formulas:
For the monthly summary:
cell I88 and copied over and down.

=SUMPRODUCT(IFERROR(((--($D$134:$BF$134=I$87))*(--($A$86=$B$135:$B$150))*(--($B88=$C$135:$C$150))*($D$135:$BF$150)),0))

cell I113 and copied over and down:

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

 

[sgibbs183]

Sorry for posting so quickly after the prior post. But I failed to add an additional filter you wanted in the formulas.
Here are updated formulas:
For the monthly summary:
cell I88 and copied over and down.

=SUMPRODUCT(IFERROR(((--($D$134:$BF$134=I$87))*(--($A$86=$B$135:$B$150))*(--($B88=$C$135:$C$150))*($D$135:$BF$150)),0))

cell I113 and copied over and down:

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

Hello,

I am just looking at the data in columns I-T now and the formula isn't giving me the YTD per month. I have put an example after row 138 what should return - please can you have a look? Thank you

YTD Budget/Spend								YTD
Budget 23/24
Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
	Capex	Project Data Input'!$E$34:$BZ$34)*(C$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	Project Data Input'!$E$34:$BZ$34)*(D$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	Project Data Input'!$E$34:$BZ$34)*(E$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	Project Data Input'!$E$34:$BZ$34)*(F$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	Project Data Input'!$E$34:$BZ$34)*(G$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	Project Data Input'!$E$34:$BZ$34)*(H$112>='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34)*(--($A$111='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50))*($B113='[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$35:$BZ$50),0))]-	12,552,763	33,117,143	31,723,166	31,380,346	13,940,197	37,348,436	35,919,670	34,504,332	15,275,051	30,028,101	16,379,157	32,807,290	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B113)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=U$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B113)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=V$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B113)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=W$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B113)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=X$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B113)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=Y$87))]-	-
	Opex	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=C$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=D$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=E$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=F$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=G$87))]-	Project Data Input'!$E$35:$BY$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BY$34=H$87))]-	144,990	307,656	289,793	270,730	144,990	307,656	307,656	307,656	144,990	247,542	144,990	289,793	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=U$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=V$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=W$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=X$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B114)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=Y$87))]-	-
	Exceptional	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=C$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=D$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=E$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=F$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=G$87))]-	Project Data Input'!$E$35:$BY$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BY$34=H$87))]-	-	-	-	-	-	-	-	-	-	-	-	-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=U$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=V$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=W$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=X$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B115)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=Y$87))]-	-
	Client Funded	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=C$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=D$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=E$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=F$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=G$87))]-	Project Data Input'!$E$35:$BY$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BY$34=H$87))]-	-	-	-	-	-	-	-	-	-	-	-	-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=U$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=V$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=W$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=X$87))]-	Project Data Input'!$E$35:$BZ$50,('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$C$35:$C$50="Budget 23/24")*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$D$35:$D$50=$B116)*('[NEW_BCM_Waterfall PED Replacement Devices_FY2324_P05 Test Version.xlsx]Project Data Input'!$E$34:$BZ$34=Y$87))]-
TOTAL		-	-	-	-	-	-	12,697,753	33,424,799	32,012,959	31,651,076	14,085,187	37,656,092	36,227,326	34,811,988	15,420,041	30,275,643	16,524,147	33,097,083	-	-	-	-	-	-

Example of how it should be

Anaplan Project	Expenditure Type	FY17	FY18	FY19	FY20	FY21	FY22	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23	Nov 23	Dec 23	Jan 24	Feb 24	Mar 24	FY23	FY24	FY25	FY26	FY27	Total
	Capex	-	-	-	-	-	-	-	-	32,966	1,075,358	2,152,939	3,271,852	4,377,336	5,454,672	6,479,672	7,266,000	8,060,253	8,834,524	8,834,524	3,718,239	-	-	-	12,552,763
	Opex	-	-	-	-	-	-	42,438	60,301	79,364	102,552	102,552	102,552	102,552	102,552	102,552	102,552	102,552	102,552	102,552	-	-	-	-	102,552
	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
TOTAL		-	-	-	-	-	-	42,438	60,301	112,330	1,177,910	2,255,491	3,374,404	4,479,888	5,557,224	6,582,224	7,368,552	8,162,805	8,937,076	8,937,076	3,718,239	-	-	-	12,655,315

Data

		Budget 23/24	Exceptional	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
		Budget 23/24	Capex	-	-	-	-	-	-	-	-	32,966	1,042,392	1,077,581	1,118,913	1,105,484	1,077,336	1,025,000	786,328	794,253	774,271	8,834,524	1,075,358	6,681,585	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	309,853	3,718,239	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	12,552,763
		Budget 23/24	Client Funded	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
		Budget 23/24	Opex	-	-	-	-	-	-	42,438	17,863	19,063	23,188	-	-	-	-	-	-	-	-	102,552	102,552	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	102,552