Calculate "this time last year" values

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
190
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
ABCD
19 JAN 2020MILES/TIME THIS WK (WK 2)252hr 00m
2DAYS GONE THIS YEARLAST WK/YR/ROLL 36500hr 00m
39YEAR TO DATE/YEARS2020 TO DATESAME DATE 2019
4MAX INDOOR BIKE DAYS LEFTCUMULATIVE MILES25752
5357CUMULATIVE HOURS249
Indoor Bike
Cell Formulas
RangeFormula
A1A1=UPPER(TEXT(TODAY(),"d mmm yyyy"))
B1B1="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) & ")"
C1C1=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Miles)
D1D1=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Times)
C2C2=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Miles)-C1
D2D2=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Times)-D1
C3C3=YEAR(NOW()) & " TO DATE"
D3D3="SAME DATE " & YEAR(NOW())-1
C4C4=SUM(LogYear_Bike_Miles)
D4D4=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())))
C5C5=SUM(LogYear_Bike_Times)
D5D5=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)
A3A3=VBADaysGone
A5A5=VBADaysLeft
Named Ranges
NameRefers ToCells
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
CellConditionCell FormatStop If True
C2Cell Value>$K$4textNO
C4Cell Value>=1000textYES
Cells with Data Validation
CellAllowCriteria
C4Whole numberbetween 67 and 69
C5Whole numberbetween 67 and 69
A1Text length=UPPER(TEXT(TODAY(),"dd mmm yyyy"))
B1Whole numberbetween 67 and 69
C1Whole number=54748458
A2,A4Whole numberbetween 67 and 69
B2Whole number=67
C2Whole number=54748458
A3Text length=TODAY()-DATE(YEAR(NOW())-1,12,31)
B3,B7,J81Any value
C3Whole numberbetween 67 and 69
A5Text length=DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"d")
D1Whole number=54748458
D2Whole number=54748458
D3Whole numberbetween 67 and 69
D4:D5Whole number=47548458



Book1
ABCD
10DATETIME (h:mm)DIST. (km)DIST. (mls)
Indoor Bike

Book1
ABCD
6708/01/20191:0023.014.3
6810/01/20191:0026.716.6
6912/01/20191:0023.514.6
7014/01/20191:0025.015.5
7116/01/20191:0024.615.3
7218/01/20191:0024.415.2
7320/01/20191:0024.915.5
7422/01/20191:0024.815.4
7524/01/20191:0022.313.8
7601/02/20191:0026.116.2
7703/02/20191:0024.215.0
7805/02/20191:0024.315.1
7907/02/20191:0026.616.5
8009/02/20191:0027.917.3
8111/02/20191:0026.916.7
8213/02/20191:0022.914.2
8321/02/20191:0025.816.0
8423/02/20191:0021.613.4
8525/02/20191:0025.115.6
8627/02/20191:0026.216.3
8701/03/20191:0022.113.7
8803/03/20191:0023.314.5
8905/03/20191:0026.316.3
9007/03/20191:0022.814.2
9109/03/20191:0022.514.0
9211/03/20191:0023.714.7
9313/03/20191:0023.714.7
9415/03/20191:0021.013.0
9516/03/20191:0024.915.5
9618/03/20191:0022.413.9
9720/03/20191:0024.014.9
9822/03/20191:0026.316.3
9924/03/20191:0023.314.5
10026/03/20191:0025.215.6
10128/03/20191:0019.412.0
10201/04/20191:0021.313.2
10303/04/20191:0024.415.2
10408/04/20191:0025.415.8
10510/04/20191:0024.315.1
10612/04/20191:0022.013.7
10714/04/20191:0026.016.1
10816/04/20191:0020.512.7
10918/04/20191:0023.114.3
11020/04/20191:0020.412.7
11122/04/20191:0023.614.7
11224/04/20191:0024.014.9
11326/04/20191:0020.612.8
11428/04/20191:0024.815.4
11530/04/20191:0021.213.2
11602/05/20191:0022.514.0
11703/05/20191:0024.815.4
11811/05/20191:0024.014.9
11913/05/20191:0024.915.5
12015/05/20191:0022.413.9
12117/05/20191:0020.512.7
12219/05/20191:0022.814.2
12321/05/20191:0024.315.1
12423/05/20191:0021.113.1
12525/05/20191:0021.513.4
12627/05/20191:0021.713.5
12729/05/20191:0022.213.8
12830/05/20191:0024.815.4
12901/06/20191:0024.915.5
13003/06/20191:0022.514.0
13105/06/20191:0023.314.5
13207/06/20191:0022.313.8
13309/06/20191:0024.815.4
13411/06/20191:0021.613.4
13513/06/20191:0023.914.8
13617/06/20191:0024.014.9
13719/06/20191:0021.713.5
13820/06/20191:0024.815.4
13922/06/20191:0023.014.3
14024/06/20191:0023.614.7
14126/06/20191:0021.213.2
14228/06/20191:0021.713.5
14303/07/20191:0020.012.4
14405/07/20191:0022.013.7
14507/07/20191:0021.613.4
14609/07/20191:0022.213.8
14711/07/20191:0021.013.0
14816/07/20191:0019.412.0
14918/07/20191:0023.114.3
15020/07/20191:0021.413.3
15122/07/20191:0021.513.4
15224/07/20191:0023.314.5
15326/07/20191:0021.013.0
15428/07/20191:0022.313.8
15530/07/20191:0020.512.7
15601/08/20191:0022.614.0
15703/08/20191:0021.313.2
15805/08/20191:0019.312.0
15907/08/20191:0023.514.6
16009/08/20191:0021.113.1
16111/08/20191:0023.014.3
16213/08/20191:0020.712.9
16315/08/20191:0023.514.6
16417/08/20191:0019.412.0
16519/08/20191:0020.012.4
16621/08/20191:0022.714.1
16723/08/20191:0020.612.8
16825/08/20191:0022.213.8
16928/08/20191:0020.012.4
17030/08/20191:0021.113.1
17101/09/20191:0020.612.8
17209/09/20191:0021.813.5
17311/09/20191:0021.013.0
17417/09/20191:0020.512.7
17519/09/20191:0023.314.5
17623/09/20191:0019.512.1
17725/09/20191:0022.714.1
17827/09/20191:0020.012.4
17929/09/20191:0019.912.4
18001/10/20191:0020.312.6
18103/10/20191:0020.112.5
18219/10/20191:0020.712.9
18306/11/20191:0020.212.5
18410/12/20191:0020.112.5
18507/01/20201:0021.213.2
18609/01/20201:0019.312.0
Indoor Bike
Cell Formulas
RangeFormula
D67:D186D67=IF(B67>0,C67*0.621,"")
Named Ranges
NameRefers ToCells
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
CellConditionCell FormatStop If True
D68:D230Cell Valuetop 10% valuestextNO
Cells with Data Validation
CellAllowCriteria
D67:D93Any value
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,407
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 31, 2004
Messages
190
Many thanks for your time guys.

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

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,961
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top