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
I simulated this scenario with a smaller data set and it behaves exactly the same.
When I add a new row to the table the values in the last formula jump one row.
I am puzzled why it does so.
 

Attachments

  • Excel screenshot5.JPG
    Excel screenshot5.JPG
    206.3 KB · Views: 0

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I find a solution for you. working on it.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use this at row 1884 then drag it up & down.
Excel Formula:
=SUMPRODUCT((OFFSET($F$4,0,0,ROW(F1884)-ROW($F$4),1))*(YEAR(OFFSET($B$4,0,0,ROW(B1884)-ROW($B$4),1))=YEAR(B1884)))
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this finally working:
Excel Formula:
=SUMPRODUCT(INDEX(OFFSET($F$4,0,0,ROW(F1884)-ROW($F$4)+1,1),0,1)*(YEAR(INDEX(OFFSET($B$4,0,0,ROW(B1884)-ROW($B$4)+1,1),0,1))=YEAR(B1884)))
 
Solution

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38

ADVERTISEMENT

Try this finally working:
Excel Formula:
=SUMPRODUCT(INDEX(OFFSET($F$4,0,0,ROW(F1884)-ROW($F$4)+1,1),0,1)*(YEAR(INDEX(OFFSET($B$4,0,0,ROW(B1884)-ROW($B$4)+1,1),0,1))=YEAR(B1884)))
Dear Maabadi,
I'm lost for words of gratitude for all your help and effort; Thank you very much for doing this!
I am at work at the moment and therefore unable to try it out but I will do so over the weekend.
This seems like a very impressive formula. I would never have been able to come up with something like that. It seems you got a great professional understanding of Excel.
Once again thank you and I will get back to you with the results soon.
Cheers,

Luke
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for feedback.
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38

ADVERTISEMENT

You're Welcome & Thanks for feedback.
Hi Maabadi,
Your solutions works great! No more green triangles and all values are correct.
However, the only thing that doesn't seem to happen is that when I click TAB at the end of the table, and a new row appears, the formulas aren't automatically copied.
I have a tick next to "Fill formulas in tables to create calculated columns" (Options - Proofing - AutoCorrect Options - Autoformat As you type), so, one would expect it should work.
I use Microsoft Excel 2016. In my other spreadsheets the formulas are automatically added when I insert a new row at the end.
Any idea what this could be caused by?
I will click on the Tick box of this post to indicate that the problem has been solved.
Thanks mate.
Luke
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Are you have tick this option.
 

Attachments

  • 1122.jpg
    1122.jpg
    37.3 KB · Views: 1

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Are you have tick this option.
Yes, all three the boxes are ticked but to no avail. Very strange isn't it?
On other spreadsheets that function works (Copying formulas automatically when a new row is inserted). Just not on this one.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,631
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sorry. this one exactly strange!!!
If you can copy data to new spreadsheet and then write formula & test it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
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