Dynamic YTD Formula Needed for Final Sheet Under Budget and Actuals

[jpalleyne]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	Budget-In separate sheet
3		Department	Account #	Jan-24	Feb-24	Mar-24	Apr-24	May-24		YTD
4	Expense
5	5100-00 · AC&CS EQUIP DEPT-SALARY (AC&CS EQUIP DEPT-SALARY)	AIR CONDITIONING DEPT-COMMERCIAL	5100-00	23,490.00	23,490.00	23,490.00	23,490.00	23,490.00		117,450.00
6	5100-01 · AC&CS-MANANGEMENT FEES	AIR CONDITIONING DEPT-COMMERCIAL	5100-01	1,980.00	1,980.00	1,980.00	1,980.00	1,980.00		9,900.00
7	5103-00 · AC&CS EQUIP DEPT-COMMISSION (AC&CS EQUIP DEPT-COMMISSION)	AIR CONDITIONING DEPT-RESIDENTIAL	5103-00	900.00	900.00	900.00	900.00	900.00		4,500.00
8
9
10	Actuals-In separate sheet
11		Account Code		Department	Type	Description	Account Code	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Total FY 2024
12	5100-00 · AC&CS EQUIP DEPT-SALARY	5100-00	5100-00 · AC&CS EQUIP DEPT-SALARY		Salaries	Payroll	5100-00	22,874.62	24,268.66	22,075.80	29,721.96	3,356.60	-	-	-	-	-	-	-	102,297.64
13	5100-01 · AC&CS-MANANGEMENT FEES	5100-01	5100-01 · AC&CS-MANANGEMENT FEES		Management Fees	Operating Expenses	5100-01	1,980.00	1,980.00	1,980.00	1,980.00	-	-	-	-	-	-	-	-	7,920.00
14
15
16
17
18
19	Final Sheet
20	PAYROLL & OPEX	Account Code	Department	Type	Description		Budget	Actual	$ Variance		Budget YTD	Actuals YTD	$ Variance
21	5100-00 · AC&CS EQUIP DEPT-SALARY	5100-00	AIR CONDITIONING DEPT-COMMERCIAL		Payroll		23,490.00	3,356.60	20,133.40
22	5100-01 · AC&CS-MANANGEMENT FEES	5100-01	AIR CONDITIONING DEPT-COMMERCIAL		Expense		1,980.00	-	1,980.00
23	5104-00 · AC&CS EQUIP DEPT-BONUS	5104-00	AIR CONDITIONING DEPT-COMMERCIAL		Payroll		-	-	-
24
25
26
27
28		Formula needed under Budget YTD and Actuals YTD based on Account Code.
29
30
31
Sheet1

 

[arun_eagertolearn]

Hi

For calculating budget YTD and actuals YTD, you may use

Budget YTD
Cell K21 (just beneath the heading 'Budget YTD')= sumifs($D5:$H5,$D$3:$H$3,"<="&TODAY())

K22=sumifs($D6:$H6,$D$3:$H$3,"<="&TODAY())
K23=sumifs($D7:$H7,$D$3:$H$3,"<="&TODAY())

Please note- H column in D3:H3, D5:H5, D6:H6 etc corresponds to May 24, in case it has to be extended till Dec 24, please change H to the appropriate one

Similarly, for Actuals YTD

Actuals YTD
L21=sumifs($H12:$S12,$H$11:$S$11,"<="&TODAY())
L22=sumifs($H13:$S13,$H$11:$S$11,"<="&TODAY())
L23=sumifs($H14:$S14,$H$11:$S$11,"<="&TODAY())

Also, you may have to populate Row 14 with '5104-00 · AC&CS EQUIP DEPT-BONUS', even if the entries are zero

In case you need till end of Apr 2024, please replace TODAY() with a cell reference, say K19
Please populate K19=Apr-2024 in that case

Thanks