Month-to-Date to Year-to-Date

[cheongmarcus]

Hi there! I've been trying to figure this out for long but i cant, and hence i need help with this.
My objective is to calculate YTD numbers from a set of MTD numbers. So example i have this set of data:

I will need to sum the figures based on these five categories

AND obtain the YTD numbers. In other words, if i input 'Mar' in cell A1, then cell B20 to B26 will be populated accordingly with the summation of Jan,Feb,Mar total.

Is there a formula to do all these at once without the need to create a YTD table?

Any help is much appreciated! Thank you!

 

[DanteAmor]

Try this

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Mar
2	MTD FIGURES	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Salaries and vagages	42	63	84	105	127	148	169	190	211	232	254	275
4	Finance costs	56	84	112	141	169	197	225	254	282	310	338	367
5	Depreciation	70	105	141	176	211	247	282	317	352	388	423	458
6	Administratio expenses	84	127	169	211	254	296	338	381	423	465	508	550
7	Finance costs	98	148	197	247	296	345	395	444	494	543	592	642
8	Depreciation	112	169	225	282	338	395	451	508	564	621	677	734
9	Impaiment losses	127	190	254	317	381	444	508	571	635	698	762	825
10	Utility expenses	141	211	282	352	423	494	564	635	705	776	847	917
11	Cost of goods sold	155	232	310	388	465	543	621	698	776	854	931	1009
12	Administratio expenses	169	254	338	423	508	592	677	762	847	931	1016	1101
13	Finance costs	183	275	367	458	550	642	734	825	917	1009	1101	1192
14	Depreciation	197	296	395	494	592	691	790	889	988	1087	1185	1284
15	Finance costs	211	317	423	529	635	741	847	952	1058	1164	1270	1376
16	Depreciation	225	338	451	564	677	790	903	1016	1129	1242	1355	1468
17	Impaiment losses	240	360	480	600	720	840	960	1080	1200	1320	1440	1560
18
19	YTD FIGURES	SUM
20	Salaries and vagages	189
21	Utility expenses	634
22	Cost of goods sold	697
23	Administratio expenses	1141
24	Finance costs	2471
25	Depreciation	2724
26	Impaiment losses	1651
Hoja7

Cell Formulas
Range	Formula
B20:B26	B20	=SUMPRODUCT((A$3:A$17=A20)*($B$3:INDEX(B$3:M$17,0,MATCH($A$1,B$2:M$2,0))))

 

[cheongmarcus]

@DanteAmor Thats genius! It works perfectly! Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[anum]

Hi Sorry to jump in on this thread - but if I were to want the month only based on this selection whats the best way please as the if statement one is too long and I'm sure there's an easier way!
I would like cols C, D & E pick up values from second screenshot based on PY, BP etc.

3697 P&L_FY21 Jan YTD.xlsx
	B	C	D	E	F	G
2	Jan
3		MTD ANALYSIS
4	EUR (000)	FY20	FBP21	ACT21	vs BP21	vs PY
5
6	Net Trade Sales	0	0	0	-	-
7
8	Currency	0	0	0	-	-
9	Cost of Other Revenue	0	0	0	-	-
10	COGS	0	0	0	-	-
11		6%	#DIV/0!	#DIV/0!
12
13	IC Sales & Services	0	0	0	-	-
14	Standard GP	0	0	0	-	-
Sheet2

Cell Formulas
Range	Formula
B2	B2	=Selections!C3
C3	C3	=UPPER(Selections!A1)&" MTD ANALYSIS"
C6:E6,C13:E13,C8:E9	C6	=M6
F6,F13:F14,F8:F10	F6	=E6-D6
G6,G13:G14,G8:G10	G6	=E6-C6
C10:E10	C10	=SUM(C7:C9)
D11:E11	D11	=D10/D6
C14:E14	C14	=C6-C10+C13

3697 P&L_FY21 Jan YTD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
19		PY
20		YTD	Year	1	2	3	4	5	6	7	8	9	10	11	12
21		2020	2020	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
22	0660140005 - Loss on Sale or Disposal of Fixed Assets	100	7,800.00	100.00	200.00	300.00	400.00	500.00	600.00	700.00	800.00	900.00	1,000.00	1,100.00	1,200.00
23	0660190001 - Miscellaneous Income - Third Party	100	3,600.00	100.00	0.00	300.00	0.00	500.00	0.00	700.00	0.00	900.00	0.00	1,100.00	0.00
24	0660230001 - Provision Collection Losses	0	4,200.00	0.00	200.00	0.00	400.00	0.00	600.00	0.00	800.00	0.00	1,000.00	0.00	1,200.00
25	0660240001 - Acquisition Amortization of Other Intangibles	100	7,550.00	100.00	200.00	250.00	400.00	450.00	600.00	650.00	800.00	850.00	1,000.00	1,050.00	1,200.00
Sheet2

Cell Formulas
Range	Formula
C22:C25	C22	=SUM(E22:INDEX(E22:P22,Selections!$C$4))
D22:D25	D22	=SUM(E22:P22)