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

[LRATOZ]

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

 

[maabadi]

Try Using These formula But change range based Your Data:

=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020))

Monthly

=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020)*(MONTH($A$2:$A$36)=1))

Book1
	A	B	C	D	E	F
1
2	1/20/2020	3		Yearly	71.8
3	1/21/2020	1		Monthly	37.5
4	1/22/2020	0.8
5	1/23/2020	4.8
6	1/24/2020	2.5
7	1/25/2020	3.7
8	1/26/2020	3.2
9	1/27/2020	3.3
10	1/28/2020	3.5
11	1/29/2020	3.7
12	1/30/2020	3.9
13	1/31/2020	4.1
14	2/1/2020	4.3
15	2/2/2020	4.5
16	2/3/2020	4.7
17	2/4/2020	4.9
18	2/5/2020	5.1
19	2/6/2020	5.3
20	2/7/2020	5.5
21	1/20/2021	0
22	1/21/2021	3.2
23	1/22/2021	4
24	1/23/2021	4.4
25	1/24/2021	4
26	1/25/2021	3.5
27	1/26/2021	1.6
28	1/27/2021	1.2
29	1/28/2021	0.8
30	1/29/2021	0.4
31	1/30/2021	1
32	1/31/2021	0.8
33	2/1/2021	4.8
34	2/2/2021	2.5
35	2/3/2021	3.7
36	2/4/2021	2.4
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020))
E3	E3	=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020)*(MONTH($A$2:$A$36)=1))

 

[LRATOZ]

Try Using These formula But change range based Your Data:

=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020))

Monthly

=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020)*(MONTH($A$2:$A$36)=1))

Book1
	A	B	C	D	E	F
1
2	1/20/2020	3		Yearly	71.8
3	1/21/2020	1		Monthly	37.5
4	1/22/2020	0.8
5	1/23/2020	4.8
6	1/24/2020	2.5
7	1/25/2020	3.7
8	1/26/2020	3.2
9	1/27/2020	3.3
10	1/28/2020	3.5
11	1/29/2020	3.7
12	1/30/2020	3.9
13	1/31/2020	4.1
14	2/1/2020	4.3
15	2/2/2020	4.5
16	2/3/2020	4.7
17	2/4/2020	4.9
18	2/5/2020	5.1
19	2/6/2020	5.3
20	2/7/2020	5.5
21	1/20/2021	0
22	1/21/2021	3.2
23	1/22/2021	4
24	1/23/2021	4.4
25	1/24/2021	4
26	1/25/2021	3.5
27	1/26/2021	1.6
28	1/27/2021	1.2
29	1/28/2021	0.8
30	1/29/2021	0.4
31	1/30/2021	1
32	1/31/2021	0.8
33	2/1/2021	4.8
34	2/2/2021	2.5
35	2/3/2021	3.7
36	2/4/2021	2.4
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=2020))
E3	E3	=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]

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
	A	B	C	D	E	F
1		Daily Rainfall	Cumulative
2	11/20/2020	3	3		Yearly	129.4
3	11/21/2020	1	4		Monthly	33.4
4	11/22/2020	0.8	4.8
5	11/23/2020	4.8	9.6
6	11/24/2020	2.5	12.1
7	11/25/2020	3.7	15.8
8	11/26/2020	3.2	19
9	11/27/2020	3.3	22.3
10	11/28/2020	3.5	25.8
11	11/29/2020	3.7	29.5
12	11/30/2020	3.9	33.4
13	12/1/2020	4.1	4.1
14	12/2/2020	4.3	8.4
15	12/3/2020	4.5	12.9
16	12/4/2020	4.7	17.6
17	12/5/2020	4.9	22.5
18	12/6/2020	5.1	27.6
19	12/7/2020	5.3	32.9
20	12/8/2020	5.5	38.4
21	12/9/2020	0	38.4
22	12/10/2020	3.2	41.6
23	12/11/2020	4	45.6
24	12/12/2020	4.4	50
25	12/13/2020	4	54
26	12/14/2020	3.5	57.5
27	12/15/2020	1.6	59.1
28	12/16/2020	1.2	60.3
29	12/17/2020	0.8	61.1
30	12/18/2020	0.4	61.5
31	12/19/2020	1	62.5
32	12/20/2020	0.8	63.3
33	12/21/2020	4.8	68.1
34	12/22/2020	2.5	70.6
35	12/23/2020	3.7	74.3
36	12/24/2020	2.4	76.7
37	12/25/2020	1	77.7
38	12/26/2020	0.8	78.5
39	12/27/2020	4.8	83.3
40	12/28/2020	2.5	85.8
41	12/29/2020	3.7	89.5
42	12/30/2020	3.2	92.7
43	12/31/2020	3.3	129.4
44	1/1/2021	3.5	3.5
45	1/2/2021	4	7.5
46	1/3/2021	3.5	11
47	1/4/2021	1.6	12.6
48	1/5/2021	1.2	13.8
49	1/6/2021	0.8	14.6
50	1/7/2021	0.4	15
51	1/8/2021	1	16
52	1/9/2021	0.8	16.8
53	1/10/2021	2.1	18.9
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(($B$2:$B$53)*(YEAR($A$2:$A$53)=YEAR(A2)))
F3	F3	=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=YEAR(A2))*(MONTH($A$2:$A$36)=MONTH(A2)))
C2:C53	C2	=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]

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
	A	B	C	D	E	F
1		Daily Rainfall	Cumulative
2	11/20/2020	3	3		Yearly	129.4
3	11/21/2020	1	4		Monthly	33.4
4	11/22/2020	0.8	4.8
5	11/23/2020	4.8	9.6
6	11/24/2020	2.5	12.1
7	11/25/2020	3.7	15.8
8	11/26/2020	3.2	19
9	11/27/2020	3.3	22.3
10	11/28/2020	3.5	25.8
11	11/29/2020	3.7	29.5
12	11/30/2020	3.9	33.4
13	12/1/2020	4.1	4.1
14	12/2/2020	4.3	8.4
15	12/3/2020	4.5	12.9
16	12/4/2020	4.7	17.6
17	12/5/2020	4.9	22.5
18	12/6/2020	5.1	27.6
19	12/7/2020	5.3	32.9
20	12/8/2020	5.5	38.4
21	12/9/2020	0	38.4
22	12/10/2020	3.2	41.6
23	12/11/2020	4	45.6
24	12/12/2020	4.4	50
25	12/13/2020	4	54
26	12/14/2020	3.5	57.5
27	12/15/2020	1.6	59.1
28	12/16/2020	1.2	60.3
29	12/17/2020	0.8	61.1
30	12/18/2020	0.4	61.5
31	12/19/2020	1	62.5
32	12/20/2020	0.8	63.3
33	12/21/2020	4.8	68.1
34	12/22/2020	2.5	70.6
35	12/23/2020	3.7	74.3
36	12/24/2020	2.4	76.7
37	12/25/2020	1	77.7
38	12/26/2020	0.8	78.5
39	12/27/2020	4.8	83.3
40	12/28/2020	2.5	85.8
41	12/29/2020	3.7	89.5
42	12/30/2020	3.2	92.7
43	12/31/2020	3.3	129.4
44	1/1/2021	3.5	3.5
45	1/2/2021	4	7.5
46	1/3/2021	3.5	11
47	1/4/2021	1.6	12.6
48	1/5/2021	1.2	13.8
49	1/6/2021	0.8	14.6
50	1/7/2021	0.4	15
51	1/8/2021	1	16
52	1/9/2021	0.8	16.8
53	1/10/2021	2.1	18.9
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(($B$2:$B$53)*(YEAR($A$2:$A$53)=YEAR(A2)))
F3	F3	=SUMPRODUCT(($B$2:$B$36)*(YEAR($A$2:$A$36)=YEAR(A2))*(MONTH($A$2:$A$36)=MONTH(A2)))
C2:C53	C2	=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

 

[maabadi]

You're Welcome

 

[LRATOZ]

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]

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]

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]

Create Table with your Data and Write again formula with selecting columns, then when you add rows, it update automatically.