# Calculate "this time last year" values

#### Ironman

##### Board Regular
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)
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### AlanY

##### Well-known Member
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
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
Many thanks for your time guys.

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

Replies
6
Views
234
Replies
31
Views
641
Replies
8
Views
120
Replies
11
Views
191
Replies
25
Views
690

1,130,181
Messages
5,640,643
Members
417,159
Latest member
Mayozero

### 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.

### Which adblocker are you using?

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

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