How to sum values of the year till 31/12 and reset to 0 on 1/1 for years running totals?

LRATOZ

New Member
Howdy,
I have been recording rainfall data since 15/11/2015. I used to have mutiple sheets for each year but I've learned that it makes more sense to have one table in one data sheet that has all the raw data in it.
So, I've got running data since 15/11/2015.
I was able to write my formulas for the monthly totals.
I've used the formula: =IF(DAY(\$B28)=1,F28,F28+G27).
This formula checks for the day and if the day is the 1st of the month then basically it resets itself and starts from scratch again (But it checks for the rainfall value on the 1st day of the month).
Now I want to sum the values of the daily rainfall until 31/12 of the year and then it needs to reset to zero again. However, if there is rainfall recorded on 1/1 it should incorporate that value.
I have been using a formula like this: =IF(AND(DAY(\$B20)=31,MONTH(\$B20)=12), SUM(\$F19:\$F20),F20+G19) but it only seesm to work till the end of the month and not the year.
I also want to see the running data value on each day of the year.
I have added a screenshot for clarity.

But as you can see it didn't do what I wanted to do when the year changes.
I know I can make sums for each individual year but that's not the point as I want to see this happening automaticcaly.
In real life this spreadsheet is much more complex as I record temperatures, wind speed, etc. So, once I got this formula correct I can then easily adjust it for the other data.
Could somebody take a close look at this and forward me your comments/suggestions?
I can forward more screenshots if required.
Thank you very much in advance and have a nice day!
Cheers,

Luke

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Well-known Member
Try Using These formula But change range based Your Data:
Excel Formula:
``=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020))``
Monthly
Excel Formula:
``=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020)*(MONTH(\$A\$2:\$A\$36)=1))``

Book1
ABCDEF
1
21/20/20203Yearly71.8
31/21/20201Monthly37.5
41/22/20200.8
51/23/20204.8
61/24/20202.5
71/25/20203.7
81/26/20203.2
91/27/20203.3
101/28/20203.5
111/29/20203.7
121/30/20203.9
131/31/20204.1
142/1/20204.3
152/2/20204.5
162/3/20204.7
172/4/20204.9
182/5/20205.1
192/6/20205.3
202/7/20205.5
211/20/20210
221/21/20213.2
231/22/20214
241/23/20214.4
251/24/20214
261/25/20213.5
271/26/20211.6
281/27/20211.2
291/28/20210.8
301/29/20210.4
311/30/20211
321/31/20210.8
332/1/20214.8
342/2/20212.5
352/3/20213.7
362/4/20212.4
Sheet1
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020))
E3E3=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020)*(MONTH(\$A\$2:\$A\$36)=1))

LRATOZ

New Member
Try Using These formula But change range based Your Data:
Excel Formula:
``=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020))``
Monthly
Excel Formula:
``=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020)*(MONTH(\$A\$2:\$A\$36)=1))``

Book1
ABCDEF
1
21/20/20203Yearly71.8
31/21/20201Monthly37.5
41/22/20200.8
51/23/20204.8
61/24/20202.5
71/25/20203.7
81/26/20203.2
91/27/20203.3
101/28/20203.5
111/29/20203.7
121/30/20203.9
131/31/20204.1
142/1/20204.3
152/2/20204.5
162/3/20204.7
172/4/20204.9
182/5/20205.1
192/6/20205.3
202/7/20205.5
211/20/20210
221/21/20213.2
231/22/20214
241/23/20214.4
251/24/20214
261/25/20213.5
271/26/20211.6
281/27/20211.2
291/28/20210.8
301/29/20210.4
311/30/20211
321/31/20210.8
332/1/20214.8
342/2/20212.5
352/3/20213.7
362/4/20212.4
Sheet1
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020))
E3E3=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=2020)*(MONTH(\$A\$2:\$A\$36)=1))
This works but it also means that I have to re-write the formula each year as it doesn't automaticaly runs over.
This would be great for a running total over all the years but it doesn't reset automaticaly on the 1st of Januari.
Thanks for your help anyway as I learned a couple of things from looking at your formulas.
Have a great day and thanks for your response!
Cheers,

Luc

Well-known Member
And if you want result at column see this:
this work dynamically and don't need change year & Month. at the end of Month , Sum Month Rainfall And at end of year, Sum Yearly Rainfall.
Book1
ABCDEF
1Daily RainfallCumulative
211/20/202033Yearly129.4
311/21/202014Monthly33.4
411/22/20200.84.8
511/23/20204.89.6
611/24/20202.512.1
711/25/20203.715.8
811/26/20203.219
911/27/20203.322.3
1011/28/20203.525.8
1111/29/20203.729.5
1211/30/20203.933.4
1312/1/20204.14.1
1412/2/20204.38.4
1512/3/20204.512.9
1612/4/20204.717.6
1712/5/20204.922.5
1812/6/20205.127.6
1912/7/20205.332.9
2012/8/20205.538.4
2112/9/2020038.4
2212/10/20203.241.6
2312/11/2020445.6
2412/12/20204.450
2512/13/2020454
2612/14/20203.557.5
2712/15/20201.659.1
2812/16/20201.260.3
2912/17/20200.861.1
3012/18/20200.461.5
3112/19/2020162.5
3212/20/20200.863.3
3312/21/20204.868.1
3412/22/20202.570.6
3512/23/20203.774.3
3612/24/20202.476.7
3712/25/2020177.7
3812/26/20200.878.5
3912/27/20204.883.3
4012/28/20202.585.8
4112/29/20203.789.5
4212/30/20203.292.7
4312/31/20203.3129.4
441/1/20213.53.5
451/2/202147.5
461/3/20213.511
471/4/20211.612.6
481/5/20211.213.8
491/6/20210.814.6
501/7/20210.415
511/8/2021116
521/9/20210.816.8
531/10/20212.118.9
Sheet1
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2)))
F3F3=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=YEAR(A2))*(MONTH(\$A\$2:\$A\$36)=MONTH(A2)))
C2:C53C2=IF(YEAR(A3)>YEAR(A2),SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2))),IF(\$A2=EOMONTH(\$A1,0),SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2))*(MONTH(\$A\$2:\$A\$53)=MONTH(A2))),IF(DAY(A2)=1,B2,SUM(C1,B2))))

LRATOZ

New Member

And if you want result at column see this:
this work dynamically and don't need change year & Month. at the end of Month , Sum Month Rainfall And at end of year, Sum Yearly Rainfall.
Book1
ABCDEF
1Daily RainfallCumulative
211/20/202033Yearly129.4
311/21/202014Monthly33.4
411/22/20200.84.8
511/23/20204.89.6
611/24/20202.512.1
711/25/20203.715.8
811/26/20203.219
911/27/20203.322.3
1011/28/20203.525.8
1111/29/20203.729.5
1211/30/20203.933.4
1312/1/20204.14.1
1412/2/20204.38.4
1512/3/20204.512.9
1612/4/20204.717.6
1712/5/20204.922.5
1812/6/20205.127.6
1912/7/20205.332.9
2012/8/20205.538.4
2112/9/2020038.4
2212/10/20203.241.6
2312/11/2020445.6
2412/12/20204.450
2512/13/2020454
2612/14/20203.557.5
2712/15/20201.659.1
2812/16/20201.260.3
2912/17/20200.861.1
3012/18/20200.461.5
3112/19/2020162.5
3212/20/20200.863.3
3312/21/20204.868.1
3412/22/20202.570.6
3512/23/20203.774.3
3612/24/20202.476.7
3712/25/2020177.7
3812/26/20200.878.5
3912/27/20204.883.3
4012/28/20202.585.8
4112/29/20203.789.5
4212/30/20203.292.7
4312/31/20203.3129.4
441/1/20213.53.5
451/2/202147.5
461/3/20213.511
471/4/20211.612.6
481/5/20211.213.8
491/6/20210.814.6
501/7/20210.415
511/8/2021116
521/9/20210.816.8
531/10/20212.118.9
Sheet1
Cell Formulas
RangeFormula
F2F2=SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2)))
F3F3=SUMPRODUCT((\$B\$2:\$B\$36)*(YEAR(\$A\$2:\$A\$36)=YEAR(A2))*(MONTH(\$A\$2:\$A\$36)=MONTH(A2)))
C2:C53C2=IF(YEAR(A3)>YEAR(A2),SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2))),IF(\$A2=EOMONTH(\$A1,0),SUMPRODUCT((\$B\$2:\$B\$53)*(YEAR(\$A\$2:\$A\$53)=YEAR(A2))*(MONTH(\$A\$2:\$A\$53)=MONTH(A2))),IF(DAY(A2)=1,B2,SUM(C1,B2))))
Thanks mate! I will give it go tomorrow and will let you know.
Much appreciated for this!
Cheers,

Luke

You're Welcome

LRATOZ

New Member

You're Welcome
I spent some time trying to implement your solution. Initially it didn't work to my liking (Most likely caused by my shortcomings in Excel) but after some perseverance and adaptions I was able to have it working!
It now resets at the start of the new year and I can see the acuumulative value each day of the year.
It rolls over without a hickup.
This is my end solution:
=SUMPRODUCT((\$F\$3:\$F49)*(YEAR(\$B\$3:\$B49)=YEAR(B49)))
Thank you very much for your time and effort. Much appreciated!
Have a nice day!
Luke

LRATOZ

New Member
OK, now I discovered a minor hiccup and it seems the above solutions work for about 90%.
The problem is that when I insert a new row at the end of table the formulas don't copy.
In every cell of the Yearly Totals there's a green triangle in the left upper corner of each cell, except for the very first and the very last cell. (Formula omits adjacent cells).
Obviously I can disable this sort of fault notification but it still doesn't solve the problem.
I realize that he very first cell is slightly different as I have to use this as starting point, but no matter what I try it stays the same result.
I have ticked the option "Fill formulas in tables to create calculated columns" (Options - Proofing - AutoCorrect Options - AutoFormat As You Type) but it still refuses to copy the formulas in the new row.
Any solutions to solve this problem?

Well-known Member
This formula work for cumulative for total year and after month changes don't reset and continue summing with previous value.
for resetting at each month , you should add also criteria for month

Well-known Member
Create Table with your Data and Write again formula with selecting columns, then when you add rows, it update automatically.

Replies
3
Views
140
Replies
1
Views
138
Replies
1
Views
368
Replies
4
Views
92
Replies
14
Views
370

1,129,423
Messages
5,636,185
Members
416,906
Latest member
DTG

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.

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