Calculate "this time last year" values

[Ironman]

Hi and a Happy New Year to you!

The first extract from my sheet cells D4:D5 SHOULD show cumulative distance and hours cycled on my indoor bike as at the same date last year. However they in fact cover the period from when I started the sheet in 2018.

The second extract shows the relevant data input, which starts from Row 11 (I have omitted rows 11:66). The correct values in D4 and D5 should be 4 and 58 respectively. I would be very grateful if an amendment or replacement formula could be provided so that the accumulated data for the corresponding date in the last calendar year is shown in these 2 cells instead.

Many thanks!

Book1
	A	B	C	D
1	9 JAN 2020	MILES/TIME THIS WK (WK 2)	25	2hr 00m
2	DAYS GONE THIS YEAR	LAST WK/YR/ROLL 365	0	0hr 00m
3	9	YEAR TO DATE/YEARS	2020 TO DATE	SAME DATE 2019
4	MAX INDOOR BIKE DAYS LEFT	CUMULATIVE MILES	25	752
5	357	CUMULATIVE HOURS	2	49
Indoor Bike

Cell Formulas
Range	Formula
A1	A1	=UPPER(TEXT(TODAY(),"d mmm yyyy"))
B1	B1	="MILES/TIME THIS WK " & "(WK " & INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) & ")"
C1	C1	=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Miles)
D1	D1	=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Times)
C2	C2	=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Miles)-C1
D2	D2	=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Times)-D1
C3	C3	=YEAR(NOW()) & " TO DATE"
D3	D3	="SAME DATE " & YEAR(NOW())-1
C4	C4	=SUM(LogYear_Bike_Miles)
D4	D4	=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
C5	C5	=SUM(LogYear_Bike_Times)
D5	D5	=ROUND(SUMIFS($B$11:$B$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*24,2)
A3	A3	=VBADaysGone
A5	A5	=VBADaysLeft

Named Ranges
Name	Refers To	Cells
All_Bike_Dates	='Indoor Bike'!$A$11:INDEX('Indoor Bike'!$A$11:$A$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))	C1:D2, C4:D5
All_Bike_Miles	='Indoor Bike'!$D$11:INDEX('Indoor Bike'!$D$11:$D$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))	C1:C2, C4:D4
All_Bike_Times	='Indoor Bike'!$B$11:INDEX('Indoor Bike'!$B$11:$B$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))	D1:D2, C5:D5
Last365_Bike_Miles	=INDEX(All_Bike_Miles,BikeLast365TopIndex):INDEX(All_Bike_Miles,BikeLast365BottomIndex)	C1:C2, C4:D4
Last365_Bike_Times	=INDEX(All_Bike_Times,BikeLast365TopIndex):INDEX(All_Bike_Times,BikeLast365BottomIndex)	D1:D2, C5:D5
LastYTD_Bike_Miles	=INDEX(All_Bike_Miles,BikeLastYearTopIndex):INDEX(All_Bike_Miles,BikeLastYTDBottomIndex)	C1:C2, C4:D4
LastYTD_Bike_Times	=INDEX(All_Bike_Times,BikeLastYearTopIndex):INDEX(All_Bike_Times,BikeLastYTDBottomIndex)	D1:D2, C5:D5
LogYear_Bike_Dates	=INDEX(All_Bike_Dates,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Dates,ROWS(All_Bike_Dates))	C1:D2, C4:D5
LogYear_Bike_Miles	=INDEX(All_Bike_Miles,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Miles,ROWS(All_Bike_Dates))	C1:C2, C4:D4
LogYear_Bike_Times	=INDEX(All_Bike_Times,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Times,ROWS(All_Bike_Dates))	D1:D2, C5:D5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Cell Value	>$K$4	text	NO
C4	Cell Value	>=1000	text	YES

Cells with Data Validation
Cell	Allow	Criteria
C4	Whole number	between 67 and 69
C5	Whole number	between 67 and 69
A1	Text length	=UPPER(TEXT(TODAY(),"dd mmm yyyy"))
B1	Whole number	between 67 and 69
C1	Whole number	=54748458
A2,A4	Whole number	between 67 and 69
B2	Whole number	=67
C2	Whole number	=54748458
A3	Text length	=TODAY()-DATE(YEAR(NOW())-1,12,31)
B3,B7,J81	Any value
C3	Whole number	between 67 and 69
A5	Text length	=DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"d")
D1	Whole number	=54748458
D2	Whole number	=54748458
D3	Whole number	between 67 and 69
D4:D5	Whole number	=47548458

Book1
	A	B	C	D
10	DATE	TIME (h:mm)	DIST. (km)	DIST. (mls)
Indoor Bike

Book1
	A	B	C	D
67	08/01/2019	1:00	23.0	14.3
68	10/01/2019	1:00	26.7	16.6
69	12/01/2019	1:00	23.5	14.6
70	14/01/2019	1:00	25.0	15.5
71	16/01/2019	1:00	24.6	15.3
72	18/01/2019	1:00	24.4	15.2
73	20/01/2019	1:00	24.9	15.5
74	22/01/2019	1:00	24.8	15.4
75	24/01/2019	1:00	22.3	13.8
76	01/02/2019	1:00	26.1	16.2
77	03/02/2019	1:00	24.2	15.0
78	05/02/2019	1:00	24.3	15.1
79	07/02/2019	1:00	26.6	16.5
80	09/02/2019	1:00	27.9	17.3
81	11/02/2019	1:00	26.9	16.7
82	13/02/2019	1:00	22.9	14.2
83	21/02/2019	1:00	25.8	16.0
84	23/02/2019	1:00	21.6	13.4
85	25/02/2019	1:00	25.1	15.6
86	27/02/2019	1:00	26.2	16.3
87	01/03/2019	1:00	22.1	13.7
88	03/03/2019	1:00	23.3	14.5
89	05/03/2019	1:00	26.3	16.3
90	07/03/2019	1:00	22.8	14.2
91	09/03/2019	1:00	22.5	14.0
92	11/03/2019	1:00	23.7	14.7
93	13/03/2019	1:00	23.7	14.7
94	15/03/2019	1:00	21.0	13.0
95	16/03/2019	1:00	24.9	15.5
96	18/03/2019	1:00	22.4	13.9
97	20/03/2019	1:00	24.0	14.9
98	22/03/2019	1:00	26.3	16.3
99	24/03/2019	1:00	23.3	14.5
100	26/03/2019	1:00	25.2	15.6
101	28/03/2019	1:00	19.4	12.0
102	01/04/2019	1:00	21.3	13.2
103	03/04/2019	1:00	24.4	15.2
104	08/04/2019	1:00	25.4	15.8
105	10/04/2019	1:00	24.3	15.1
106	12/04/2019	1:00	22.0	13.7
107	14/04/2019	1:00	26.0	16.1
108	16/04/2019	1:00	20.5	12.7
109	18/04/2019	1:00	23.1	14.3
110	20/04/2019	1:00	20.4	12.7
111	22/04/2019	1:00	23.6	14.7
112	24/04/2019	1:00	24.0	14.9
113	26/04/2019	1:00	20.6	12.8
114	28/04/2019	1:00	24.8	15.4
115	30/04/2019	1:00	21.2	13.2
116	02/05/2019	1:00	22.5	14.0
117	03/05/2019	1:00	24.8	15.4
118	11/05/2019	1:00	24.0	14.9
119	13/05/2019	1:00	24.9	15.5
120	15/05/2019	1:00	22.4	13.9
121	17/05/2019	1:00	20.5	12.7
122	19/05/2019	1:00	22.8	14.2
123	21/05/2019	1:00	24.3	15.1
124	23/05/2019	1:00	21.1	13.1
125	25/05/2019	1:00	21.5	13.4
126	27/05/2019	1:00	21.7	13.5
127	29/05/2019	1:00	22.2	13.8
128	30/05/2019	1:00	24.8	15.4
129	01/06/2019	1:00	24.9	15.5
130	03/06/2019	1:00	22.5	14.0
131	05/06/2019	1:00	23.3	14.5
132	07/06/2019	1:00	22.3	13.8
133	09/06/2019	1:00	24.8	15.4
134	11/06/2019	1:00	21.6	13.4
135	13/06/2019	1:00	23.9	14.8
136	17/06/2019	1:00	24.0	14.9
137	19/06/2019	1:00	21.7	13.5
138	20/06/2019	1:00	24.8	15.4
139	22/06/2019	1:00	23.0	14.3
140	24/06/2019	1:00	23.6	14.7
141	26/06/2019	1:00	21.2	13.2
142	28/06/2019	1:00	21.7	13.5
143	03/07/2019	1:00	20.0	12.4
144	05/07/2019	1:00	22.0	13.7
145	07/07/2019	1:00	21.6	13.4
146	09/07/2019	1:00	22.2	13.8
147	11/07/2019	1:00	21.0	13.0
148	16/07/2019	1:00	19.4	12.0
149	18/07/2019	1:00	23.1	14.3
150	20/07/2019	1:00	21.4	13.3
151	22/07/2019	1:00	21.5	13.4
152	24/07/2019	1:00	23.3	14.5
153	26/07/2019	1:00	21.0	13.0
154	28/07/2019	1:00	22.3	13.8
155	30/07/2019	1:00	20.5	12.7
156	01/08/2019	1:00	22.6	14.0
157	03/08/2019	1:00	21.3	13.2
158	05/08/2019	1:00	19.3	12.0
159	07/08/2019	1:00	23.5	14.6
160	09/08/2019	1:00	21.1	13.1
161	11/08/2019	1:00	23.0	14.3
162	13/08/2019	1:00	20.7	12.9
163	15/08/2019	1:00	23.5	14.6
164	17/08/2019	1:00	19.4	12.0
165	19/08/2019	1:00	20.0	12.4
166	21/08/2019	1:00	22.7	14.1
167	23/08/2019	1:00	20.6	12.8
168	25/08/2019	1:00	22.2	13.8
169	28/08/2019	1:00	20.0	12.4
170	30/08/2019	1:00	21.1	13.1
171	01/09/2019	1:00	20.6	12.8
172	09/09/2019	1:00	21.8	13.5
173	11/09/2019	1:00	21.0	13.0
174	17/09/2019	1:00	20.5	12.7
175	19/09/2019	1:00	23.3	14.5
176	23/09/2019	1:00	19.5	12.1
177	25/09/2019	1:00	22.7	14.1
178	27/09/2019	1:00	20.0	12.4
179	29/09/2019	1:00	19.9	12.4
180	01/10/2019	1:00	20.3	12.6
181	03/10/2019	1:00	20.1	12.5
182	19/10/2019	1:00	20.7	12.9
183	06/11/2019	1:00	20.2	12.5
184	10/12/2019	1:00	20.1	12.5
185	07/01/2020	1:00	21.2	13.2
186	09/01/2020	1:00	19.3	12.0
Indoor Bike

Cell Formulas
Range	Formula
D67:D186	D67	=IF(B67>0,C67*0.621,"")

Named Ranges
Name	Refers To	Cells
All_Bike_Times	='Indoor Bike'!$B$11:INDEX('Indoor Bike'!$B$11:$B$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))	D67:D186
Last365_Bike_Times	=INDEX(All_Bike_Times,BikeLast365TopIndex):INDEX(All_Bike_Times,BikeLast365BottomIndex)	D68:D186
LastYTD_Bike_Times	=INDEX(All_Bike_Times,BikeLastYearTopIndex):INDEX(All_Bike_Times,BikeLastYTDBottomIndex)	D67:D184
LogYear_Bike_Times	=INDEX(All_Bike_Times,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Times,ROWS(All_Bike_Dates))	D185:D186

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D68:D230	Cell Value	top 10% values	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D67:D93	Any value

 

[AlanY]

D4, shouldn't it be >= Year-1?

=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))

 

[steve the fish]

Not quite sure what that min is there to do? Anyway you need a start date and an end date. Taking today you need to start at 1/1/2019 and end 10/1/2019 (or 1/10/2019 if you are american). So try:

=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&DATE(YEAR($A$1)-1,1,1),$A$11:$A$4998,"<="&DATE(YEAR($A$1)-1,MONTH($A$1),DAY($A$1)))

You may as well take advantage of the fact you already have todays date in A1.

 

[Ironman]

Many thanks for your time guys.

Steve - your formula works perfectly (with $D$ substituted for $B$ for D5).