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
38
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
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
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.
I'm sorry but I had forgotten to attach the screenshot. Here it is:
 

Attachments

  • Excel screenshot2.JPG
    Excel screenshot2.JPG
    115.8 KB · Views: 4

Some videos you may like

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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,630
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you Select all of your Data and Then Insert Table?
Are you Update formula I told at Post #10?
I think you have input wrong range for formula. Please upload your formula at cell H884 to I see it.
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Are you Select all of your Data and Then Insert Table?
Are you Update formula I told at Post #10?
I think you have input wrong range for formula. Please upload your formula at cell H884 to I see it.
=SUMPRODUCT(($F$4:$F1885)*(YEAR($B$4:$B1885)=YEAR(B1884)))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,630
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Why at row 1884 your rang is 1885 , change it to 1884
Excel Formula:
=SUMPRODUCT(($F$4:$F1884)*(YEAR($B$4:$B1884)=YEAR(B1884)))
And drag it up to first cell & also drag it down.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,630
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You see last formula cell range different than above and it ranges adding two rows from 1883 to 1885 (it should be 1884)
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
And now a screenshot that shows the formulas in all cells
OK, I copied the cell formula from H1884 up (After I modified everything to 1884 instead of 1885)
and then I dragged it down again.
The funny thing is that it changed the vallues inside the formula back to 1885 instead of 1884.
I got no idea why. Everywhere else in the range it shows the correct values. Just the last cell is modified to the next row but it doesn't copy the formula.
I'm puzzled beyond belief.
I will simulate this on another page with a limited amount of values and see if I can replicate the same phenomena.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,630
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Clear formula at row 1884 & again drag it from 1880 or 1881.
You should test it to find problem?
I wish you found problem. I cann't help from here. sorry.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,344
Members
416,096
Latest member
forevans

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