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

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
36
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.
Excel screenshot.JPG


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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,345
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 17, 2014
Messages
36
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))
Thanks Maabadi.
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,345
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Aug 17, 2014
Messages
36

ADVERTISEMENT

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
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
36

ADVERTISEMENT

You're Welcome
Dear Maadabi,
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
Joined
Aug 17, 2014
Messages
36
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?
Thanks in advance.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,345
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Your 're Welcome.
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,345
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Create Table with your Data and Write again formula with selecting columns, then when you add rows, it update automatically.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,051
Members
414,281
Latest member
Engjamal2021

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