Cumulative monthly total

[Muthukrishnan V]

Excel 2007

Column A contain dates chronologically from
01-01-2020 to 31-03-2020 for 3 months
in dd-mm-yyyy format.

Column B in numerical. On daily basis expenses
for the particular dates are entered

Column C in numerical. Calculates cumulative
total expenses.

My request: Column C should start afresh every
month. For example upto 31-01-2020,
total cumulative expenses 80000. On
01-02-2020 (Feb Begins); Col B expenses
that day is 1500. Now Col C cumulative
total shows 81500.

Here, I want Col C to show 1500 (being
cumulative total for Feb.

Request formula. Thanking you,

 

[Peter_SSs]

Do you mean a monthly progressive average like this?

20 02 27.xlsm
	A	B	C	D
1	Date	Expense	Cum Total	Month Cum Average
2	01-Jan-20	1	1	1
3	02-Jan-20	2	3	1.5
4	03-Jan-20	3	6	2
5	04-Jan-20	4	10	2.5
6	05-Jan-20	5	15	3
7	06-Jan-20	6	21	3.5
8	31-Jan-20	7	28	4
9	01-Feb-20	8	8	8
10	02-Feb-20	9	17	8.5
11	05-Feb-20	10	27	9
12	02-Mar-20	11
13	03-Mar-20	1
Cum Total

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2>TODAY(),"",B2+IF(TEXT(A2,"m")=TEXT(A1,"m"),C1,0))
D2:D13	D2	=IF(C2="","",C2/SUMPRODUCT(--(TEXT(A$2:A2,"m")=TEXT(A2,"m"))))

 

[Muthukrishnan V]

Dear Peter Sir, My required output in your above example,

D9 should show 0.27 (C9/27 - Feb 29 days); & likewise
D11 should show 0.93 (C11/29)

Thank you Sir

 

[Peter_SSs]

So like this then?

20 02 27.xlsm
	A	B	C	D
1	Date	Expense	Cum Total	Month Cum Average
2	01-Jan-20	1	1	0.032258065
3	02-Jan-20	2	3	0.096774194
4	03-Jan-20	3	6	0.193548387
5	04-Jan-20	4	10	0.322580645
6	05-Jan-20	5	15	0.483870968
7	06-Jan-20	6	21	0.677419355
8	31-Jan-20	7	28	0.903225806
9	01-Feb-20	8	8	0.275862069
10	02-Feb-20	9	17	0.586206897
11	05-Feb-20	10	27	0.931034483
12	02-Mar-20	11
13	03-Mar-20	1
Cum Total

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2>TODAY(),"",B2+IF(TEXT(A2,"m")=TEXT(A1,"m"),C1,0))
D2:D13	D2	=IF(C2="","",C2/DAY(EOMONTH(A2,0)))

 

[Muthukrishnan V]

Exactly Sir, Thank you

 

[Peter_SSs]

Exactly Sir, Thank you

You're welcome.

 

[AhoyNC]

Maybe something like this. Formula in D2 will give a running average per month or you could do something like formula in G2 to just average per month (cells in column F are formatted as MMM-YYY).
NOTE: Both of these formulas are array formulas and must be entered with CTRL-SHIFT-ENTER. then draw formula down as needed.

Book1
	A	B	C	D	E	F	G
1	Date	Expense	Cum Total	Cum Avg		Month/Yr	Aver
2	1/1/2020	1	1	1.00		1/1/2020	4.00
3	1/2/2020	2	3	1.50		2/1/2020	9.00
4	1/3/2020	3	6	2.00		3/1/2020
5	1/4/2020	4	10	2.50		4/1/2020
6	1/5/2020	5	15	3.00		5/1/2020
7	1/6/2020	6	21	3.50		6/1/2020
8	1/31/2020	7	28	4.00
9	2/1/2020	8	8	8.00
10	2/2/2020	9	17	8.50
11	2/5/2020	10	27	9.00
12	3/2/2020	11
13	3/3/2020	1
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2>TODAY(),"",SUMPRODUCT(--(MONTH($A$2:A2)=MONTH(A2)),($B$2:B2)))
D2:D13	D2	{=IF(A2>TODAY(),"",AVERAGE(IF(MONTH($A$2:A2)=MONTH(A2),$B$2:B2)))}
G2:G7	G2	{=IF(MONTH(F2)>MONTH(TODAY()),"",AVERAGE(IF(MONTH($A$2:$A$13)=MONTH(F2),$B$2:$B$13)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.