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
Sorry. this one exactly strange!!!
If you can copy data to new spreadsheet and then write formula & test it.
I am still puzzled.
I opened a new file and started from scratch but ended up with the same result.
When I press tab on the last cell of the last row of the table then a new row is added. The formulas do get copied but the references change to another cell (It jumps one line ahead).
When I manually change the formula then it works.
I was going to attach my file to this posting but the system doesn't allow me to do so. Is there a way I can attach the file to this post or email it to a communal depository?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
OK, This is what's happening:
Say I'm in the last cell (F1902) of the table then I can see following formula:
=SUMPRODUCT(INDEX(OFFSET($F$4,0,0,ROW(F1902)-ROW($F$4)+1,1),0,1)*(YEAR(INDEX(OFFSET($B$4,0,0,ROW(B1902)-ROW($B$4)+1,1),0,1))=YEAR(B1902)))
Then, I click TAB. A new row is added and the formula is copied automatically.

However, the formula in F1903 is now:
=SUMPRODUCT(INDEX(OFFSET($G$6,0,0,ROW(F1903)-ROW($G$6)+1,1),0,1)*(YEAR(INDEX(OFFSET($C$6,0,0,ROW(B1903)-ROW($C$6)+1,1),0,1))=YEAR(B1903)))
So, it seems that every absolute referenced cell is changing by jumping 1 column and 2 rows.
Now, the one million dollar question is: Why doesn't it keep its absolute referenced cell? as $F$4 and $B$4?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,801
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload your example file with XL2BB ADDIN at above of reply section OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
 

maabadi

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

ADVERTISEMENT

Do this Steps One By One. (for me working)
1. Select total table ( hit on one cell at table and Press CTRL+A 2 times to select table with header).
2. go to Design Tab and Select Convert to Range
3. Back to Home tab, Select Clear and then Clear Formats
4. Press CTRL+T
5. Change Table Color format to what you want from Template ( I select 2nd at First Row).
6. Now Test again your formula filling.

If don't Succeeded , I upload your files to you again
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Do this Steps One By One. (for me working)
1. Select total table ( hit on one cell at table and Press CTRL+A 2 times to select table with header).
2. go to Design Tab and Select Convert to Range
3. Back to Home tab, Select Clear and then Clear Formats
4. Press CTRL+T
5. Change Table Color format to what you want from Template ( I select 2nd at First Row).
6. Now Test again your formula filling.

If don't Succeeded , I upload your files to you again
All good maabadi! You're a hero. If you would be around I would buy you a beer.
The problem has been sold and it is now 100% working.
I would not have been able to sort this out without your help and perseverance.
I flagged this post as problem solved. I hope somebody can benefit from this as well.
Have a great day and once again thank you very much!
I wish I could do something in return.
Cheers,

Luke
 

maabadi

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

Watch MrExcel Video

Forum statistics

Threads
1,129,556
Messages
5,637,033
Members
416,954
Latest member
Gohar hussain

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