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,

 

[AhoyNC]

Try:
Copy formula down as needed.

Book1
	A	B	C
1	Date	Expense	Cum Total
2	1/1/2020	1	1
3	1/2/2020	2	3
4	1/3/2020	3	6
5	1/4/2020	4	10
6	1/5/2020	5	15
7	1/6/2020	6	21
8	1/31/2020	7	28
9	2/1/2020	8	8
10	2/2/2020	9	17
11	2/5/2020	10	27
12	3/2/2020	11	11
13	3/3/2020	1	12
Sheet3

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

 

[Peter_SSs]

Another way

A_MrExcel.xlsm
	A	B	C
1	Date	Expense	Cum Total
2	1/01/2020	1	1
3	2/01/2020	2	3
4	3/01/2020	3	6
5	4/01/2020	4	10
6	5/01/2020	5	15
7	6/01/2020	6	21
8	31/01/2020	7	28
9	1/02/2020	8	8
10	2/02/2020	9	17
11	5/02/2020	10	27
12	2/03/2020	11	11
13	3/03/2020	1	12
Monthly cumulative

Cell Formulas
Range	Formula
C2:C13	C2	=IF(TEXT(A2,"m")=TEXT(A1,"m"),C1+B2,B2)

 

[lrobbo314]

Power Query isn't the better solution in this case. It's a lot more convoluted, but it was a fun exercise.

Book1
	A	B	C	D	E	F
1	Date	Expense		Date	Expense	Running Total
2	1/1/2020	1		1/1/2020	1	1
3	1/2/2020	2		1/2/2020	2	3
4	1/3/2020	3		1/3/2020	3	6
5	1/4/2020	4		1/4/2020	4	10
6	1/5/2020	5		1/5/2020	5	15
7	1/6/2020	6		1/6/2020	6	21
8				1/31/2020	7	28
9				2/1/2020	8	8
10				2/2/2020	9	17
11				2/5/2020	10	27
12				3/2/2020	11	11
13				3/3/2020	1	12
Sheet8

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expense", Int64.Type}, {"Cum Total", Int64.Type}}),
    Month = Table.AddColumn(Type, "Month", each Date.MonthName([Date])),
    Group = Table.Group(Month, {"Month"}, {{"group", each _, type table [Date=date, Expense=number, Cum Total=number, Month=text]}}),
    addIndex = Table.TransformColumns(Group,{"group",each Table.AddIndexColumn(_,"Custom",1)}),
    runningTotal = Table.TransformColumns(addIndex,{"group",(tbl)=> Table.AddColumn(tbl,"Running Total",each
        List.Sum(List.Range(tbl[Expense],0,[Custom]))
        )}),
    Rxpand = Table.ExpandTableColumn(runningTotal, "group", {"Date", "Expense", "Running Total"}, {"Date", "Expense", "Running Total"}),
    RC = Table.RemoveColumns(Rxpand,{"Month"})
in
    RC

 

[Muthukrishnan V]

Thank you Sirs,

 

[Peter_SSs]

You're welcome.

 

[Muthukrishnan V]

Excel 2007

With Solution with sumproduct formula, it works

fine. But I observe that as of now (today being 27-02-2020,

dd-mm-yyyy), there are entries in Column B (daily entries)

and column C shows correct cumulative balances. There

are no entries in future dates, i.e. from 28-02-2020 to

31-02-2020. From 28-02-2020 Column C shows balances.

27-02-2020 Cumulative balance is 157900 and this is

appearing for future dates 28-02-2020 to 29-02-2020.

My request: Kindly give me an amended formula so that

Column C shows blank for future dates.

 

[AhoyNC]

Using SUMPRODUCT formula try:

Book1
	A	B	C
1	Date	Expense	Cum Total
2	1/1/2020	1	1
3	1/2/2020	2	3
4	1/3/2020	3	6
5	1/4/2020	4	10
6	1/5/2020	5	15
7	1/6/2020	6	21
8	1/31/2020	7	28
9	2/1/2020	8	8
10	2/2/2020	9	17
11	2/5/2020	10	27
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)))

 

[Peter_SSs]

Using IF formula, try

20 02 27.xlsm
	A	B	C
1	Date	Expense	Cum Total
2	01-Jan-20	1	1
3	02-Jan-20	2	3
4	03-Jan-20	3	6
5	04-Jan-20	4	10
6	05-Jan-20	5	15
7	06-Jan-20	6	21
8	31-Jan-20	7	28
9	01-Feb-20	8	8
10	02-Feb-20	9	17
11	05-Feb-20	10	27
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))

 

[Muthukrishnan V]

Excel 2007.

Thank you Sirs, for your kind and valuable help. I have

one more question to ask. To arrive at average expenses

based on cumulative balance, in Column D2, I have

entered formula =IF(COUNT(B2:C2),AVERAGE($B$1:$B2),"")

and carried this upto D95. Cell B1 is blank.


This gives average of the cumulative expenses alright, but

reckons from 01-01-2020. How to amend this formula so that

the average is for particular months. Thank you,