# 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

#### 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?
I'm sorry but I had forgotten to attach the screenshot. Here it is:

#### Attachments

• Excel screenshot2.JPG
115.8 KB · Views: 4

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

##### Well-known Member
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
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)))

#### LRATOZ

##### New Member
=SUMPRODUCT((\$F\$4:\$F1885)*(YEAR(\$B\$4:\$B1885)=YEAR(B1884)))
And also another screenshot:

#### Attachments

• Excel screenshot3.JPG
150.1 KB · Views: 1

##### Well-known Member

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.

#### LRATOZ

##### New Member
And also another screenshot:
And now a screenshot that shows the formulas in all cells

#### Attachments

• Excel screenshot4 (Minimized).JPG
187.7 KB · Views: 1

##### Well-known Member

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

##### Well-known Member
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.

Replies
3
Views
132
Replies
1
Views
120
Replies
1
Views
345
Replies
4
Views
87
Replies
14
Views
351

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?

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