New formula for cumulative values

[Dave Patton]

The examples shows a recap of bank account values. The results are correct but the cumulative value formulas are copied down.
The number of banks may increase and the number of months will increase.
Can I replace the formulas that provides the cumulative values with one formula?

Bk_InfoV1.xlsx
	A	B	C	D	E	F	G	H	I	J
1			Outstanding	Per
2	Bank Name	Per records	Amounts	Bank	Difference
3	a	12,000.00	0.00	12,000.00	0.00
4	b	16,538.23	-4,650.00	11,888.23	0.00
5	c	154.36	0.00	154.36	0.00
6	m	-1,330.29	0.00	-1,330.29	0.00
7	v	-4,704.00	744.00	-3,960.00	0.00
8	Total	22,658.30	-3,906.00	18,752.30	0.00
9					J
10
11
12
13		202312	202401	202402	202403	202404	202405	202406	202407	Total
14	a	10,000.00		2,000.00						12,000.00
15	b	10,398.50	-500.00	-15.43	405.16	2,200.00	-600.00	4,650.00		16,538.23
16	c	371.56		-108.60	-108.60					154.36
17	m		-1,299.99	-30.30						-1,330.29
18	v	-3,960.00				-425.00		-105.00	-214.00	-4,704.00
19	Total	16,810.06	-1,799.99	1,845.67	296.56	1,775.00	-600.00	4,545.00	-214.00	22,658.30
20		Bank by month	# of Months	8
21
22
23	Cumulative	2023-12	2024-01	2024-02	2024-03	2024-04	2024-05	2024-06	2024-07
24	a	10,000.00	10,000.00	12,000.00	12,000.00	12,000.00	12,000.00	12,000.00	12,000.00
25	b	10,398.50	9,898.50	9,883.07	10,288.23	12,488.23	11,888.23	16,538.23	16,538.23
26	c	371.56	371.56	262.96	154.36	154.36	154.36	154.36	154.36
27	m	0.00	-1,299.99	-1,330.29	-1,330.29	-1,330.29	-1,330.29	-1,330.29	-1,330.29
28	v	-3,960.00	-3,960.00	-3,960.00	-3,960.00	-4,385.00	-4,385.00	-4,490.00	-4,704.00
29	Total	16,810.06	15,010.07	16,855.74	17,152.30	18,927.30	18,327.30	22,872.30	22,658.30
30
BK_Rec and recap

Cell Formulas
Range	Formula
A3:B8	A3	=GROUPBY(T_[BK],T_[Amount],SUM)
C3:C7	C3	=-SUM((T_[BK]=A3)*T_[Amount]*(T_[Cleared]=""))
C8:E8	C8	=SUM(C3:C7)
E3:E7	E3	=B3+C3-D3
E9	E9	=IF(E8=0, "J","L")
A13:J19	A13	=PIVOTBY(T_[BK],TEXT(T_[Date],"yyyymm"),T_[Amount],SUM)
D20	D20	=COUNTA($B$13:$Z$13)-1
A24:A29	A24	=A14:A19
B23:I23	B23	=LET(m,COUNTA(B13:AA13)-1,EOMONTH(DATE(2023,12,0),SEQUENCE(,m)))
B24:I28	B24	=TAKE(SCAN(0,B14:Z14,SUM),,$D$20)
B29:J29	B29	=LET(a,SUM(B24:B28),IF(a,a,""))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E9	Expression	=E9="L"	text	NO
E9	Expression	="G7=""J"""	text	NO
H7	Expression	=H7="L"	text	NO
H7	Expression	="G7=""J"""	text	NO

 

[kvsrinivasamurthy]

It is not clear. Can you explain a bit detailed way.

 

[Dave Patton]

The formula in B24 calculates the accumulated total for that row and the formula is copied down.
Range A24:A28 is just a copy of A14:A18.
The formulas yield the correct values.

Is it possible to replace the formulas in A23:I29 with a more sophisticated (newer) formula or formulas?

 

[kvsrinivasamurthy]

In B23,

=LET(hr,B13:I13,a,B14:I18,za,BYCOL(hr,LAMBDA(cha,LEFT(cha,4)&"-"&RIGHT(cha,2))),zb,MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,SUM(INDEX(a,r,1):INDEX(a,r,c)))),zc,MAKEARRAY(1,COLUMNS(zb),LAMBDA(ro,clm,SUM(INDEX(zb,,clm)))),VSTACK(za,zb,zc))

 

[Cubist]

Something like this perhaps:

Book1
	A	B	C	D	E	F	G	H	I
13
14	a	10000		2000					12000
15	b	10398.5	-500	-15.43	405.16	2200	-600	4650
16	c	371.56		-108.6	-108.6
17	m		-1299.99	-30.3
18	v	-3960				-425		-105	-214
19
20
21
22
23
24	a	10000	10000	12000	12000	12000	12000	12000	24000
25	b	10398.5	9898.5	9883.07	10288.23	12488.23	11888.23	16538.23	16538.23
26	c	371.56	371.56	262.96	154.36	154.36	154.36	154.36	154.36
27	m	0	-1299.99	-1330.29	-1330.29	-1330.29	-1330.29	-1330.29	-1330.29
28	v	-3960	-3960	-3960	-3960	-4385	-4385	-4490	-4704
Sheet3

Cell Formulas
Range	Formula
B24:I28	B24	=MAKEARRAY(5,8,LAMBDA(r,c,SUM(INDEX(B14:I18,r,1):INDEX(B14:I18,r,c))))
Dynamic array formulas.

 

[Dave Patton]

Thanks very much kvsrinivasamurthy and Cubist; both suggestions are excellent.

They both work but I will have review each one carefully. I wish I could mark them both as solutions.

 

[djclements]

Just for fun, a few more options could be:

=LET(
    vals, IFERROR(--DROP(DROP(A13#,1,1),-1,-1),0),
    DROP(SCAN(0,EXPAND(vals,,COLUMNS(vals)+1),LAMBDA(a,v,IFERROR(a+v,0))),,-1)
)

Or:

=LET(
    vals, IFERROR(--DROP(DROP(A13#,1,1),-1,-1),0),
    n, SEQUENCE(COLUMNS(vals)),
    MMULT(vals,--(n<=TOROW(n)))
)

Or direct from the source table (with header and total row):

=LET(
    eom, EOMONTH(+T_[Date],0),
    unq, TOROW(UNIQUE(SORT(eom))),
    VSTACK(
        HSTACK("Cumulative",TEXT(unq,"yyyy-mm")),
        GROUPBY(T_[BK],(eom<=unq)*T_[Amount],SUM,0,1)
    )
)