Calculate YTD automatically

[GarryFreeman]

Hoping someone can help me

I am trying to create a file that will always show the YTD based on the last data entered for this year. So as the weeks go by and data is populated the sum adds the previous week to each year based on the latest week populated this year.

Hope this makes sense? Thanks in advance

Garry

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		04/01/2021	11/01/2021	18/01/2021	25/01/2021	26/01/2021	27/01/2021	28/01/2021		YTD	2021 V 2020	2021 V 2019	2021 V 2018
2		WK1	WK2	WK3	WK4	WK5	WK6	WK7
3	2018	£ 42,529	£ 42,536	£ 42,543	£ 42,550	£ 42,550	£ 42,550	£ -		£ 170,158
4	2019	£ 43,829	£ 43,836	£ 43,843	£ 43,850	£ 43,850	£ 43,850	£ -		£ 175,358
5	2020	£ 44,200	£ 44,207	£ 44,214	£ 44,221	£ 44,221	£ 44,221	£ -		£ 176,842
6	2021	£ 45,250	£ 45,257	£ 45,264	£ 45,264	£ -	£ -	£ -		£ 181,035	£ 4,193	£ 5,677	£ 10,877
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=SUM(B3:E3)
K6	K6	=J6-J5
L6	L6	=J6-J4
M6	M6	=J6-J3

 

[FormR]

Hi, here is one option you can try.

Book4
	A	B	C	D	E	F	G	H	I	J
1		44200	44207	44214	44221	44222	44223	44224		YTD
2		WK1	WK2	WK3	WK4	WK5	WK6	WK7
3	2018	42529	42536	42543	42550	42550	42550	0		170158
4	2019	43829	43836	43843	43850	43850	43850	0		175358
5	2020	44200	44207	44214	44221	44221	44221	0		176842
6	2021	45250	45257	45264	45264	0	0	0		181035
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=SUM(B3:INDEX(B3:H3,COUNTIFS($B$6:$H$6,">0")))

 

[GarryFreeman]

Perfect, thank you very much.