How to running subtotal and reset based on a day of the month?

[Alex20850]

I have a list of amounts that need running subtotals UNTIL a new time period is started on the 6th of the month.
Then, the running subtotals restart at 0 and collect until the 6th of the next month.

Sample Data:

Date	Amount	Running Totals
1/6/21	100	100
1/7/21	50	150
...
2/6/21	75	75
2/7/21	25	100

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C
1	Date	Amount	Running monthly
2	1/6/2021	19	19
3	1/7/2021	52	71
4	1/8/2021	12	83
5	1/9/2021	61	144
6	1/10/2021	64	208
7	1/11/2021	15	223
8	1/12/2021	52	275
9	1/13/2021	58	333
10	1/14/2021	21	354
11	1/15/2021	96	450
12	1/16/2021	50	500
13	1/17/2021	94	594
14	1/18/2021	14	608
15	1/19/2021	12	620
16	1/20/2021	43	663
17	1/21/2021	77	740
18	1/22/2021	98	838
19	1/23/2021	60	898
20	1/24/2021	59	957
21	1/25/2021	59	1016
22	1/26/2021	43	1059
23	1/27/2021	89	1148
24	1/28/2021	77	1225
25	1/29/2021	50	1275
26	1/30/2021	95	1370
27	1/31/2021	92	1462
28	2/1/2021	46	1508
29	2/2/2021	96	1604
30	2/3/2021	96	1700
31	2/4/2021	71	1771
32	2/5/2021	83	1854
33	2/6/2021	14	14
34	2/7/2021	65	79
35	2/8/2021	61	140
36	2/9/2021	21	161
37	2/10/2021	82	243
38	2/11/2021	88	331
39	2/12/2021	31	362
40	2/13/2021	11	373
41	2/14/2021	69	442
42	2/15/2021	99	541
43	2/16/2021	84	625
44	2/17/2021	37	662
45	2/18/2021	41	703
46	2/19/2021	35	738
47	2/20/2021	21	759
48	2/21/2021	98	857
49	2/22/2021	15	872
50	2/23/2021	61	933
51	2/24/2021	34	967
52	2/25/2021	83	1050
53	2/26/2021	26	1076
54	2/27/2021	45	1121
55	2/28/2021	73	1194
56	3/1/2021	81	1275
57	3/2/2021	21	1296
58	3/3/2021	57	1353
59	3/4/2021	20	1373
60	3/5/2021	20	1393
61	3/6/2021	15	15
62	3/7/2021	66	81
63	3/8/2021	69	150
64	3/9/2021	83	233
Sheet23

Cell Formulas
Range	Formula
C2:C64	C2	=SUMPRODUCT(B$2:B2,--(MONTH(A$2:A2-5)=MONTH(A2-5)),--(YEAR(A$2:A2-5)=YEAR(A2-5)))

 

[Alex20850]

Wow, works. Not familiar with the formulas, but I will look into them. Thanks!

 

[jasonb75]

The solution to this depends on the consistency of the data. If there is always one entry per day (with no days missing) then this will work. If either of those points is not the case then it will need something less simple.

Book1
	A	B	C
1	Date	Amount	R/T
2	06/01/2021	100	100
3	07/01/2021	50	150
4	06/02/2021	75	75
5	07/02/2021	25	100
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(DAY(A2)=6,B2,B2+C1)

 

[JamesCanale]

You can put a whole world inside of SUMPRODUCT.

 

[Joe4]

I have merged your two duplicate threads together.
Please do not start two threads on the same topic in the future.
Duplicate threads will typically be locked or deleted, as per forum rules.