Running total calculation

[edvrdsdvskj]

Hello,

I need a formula for running a total calculation array which would be used in XLOOKUP formula to find the number based on some criteria.
The formula I've tried to create is listed in the cell L2. Unfortunately, but can't use the helper column or refer to other sheets.

Any ideas or in general is it possible using only formulas?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Username	Period	S% units/total	L% units/total	XL% units/total	Total% unit_count	Running total%		2023-01	RunTotal	Formula which work	Doesn't work
2	User-1	2023-01	0.0%	1.4%	47.7%	1.0%	1.0%		0.0%	0.0%	0.0%	#VALUE!
3	User-2	2023-01	1.0%	2.9%	3.6%	1.2%	2.2%		1.0%	1.0%	1.0%	#VALUE!
4	User-3	2023-01	2.7%	10.5%	0.5%	3.1%	5.3%		2.7%	3.7%	3.7%	#VALUE!
5	User-4	2023-01	12.0%	22.5%	15.9%	12.8%	18.1%		12.0%	15.7%	15.7%	#VALUE!
6	User-5	2023-01	13.2%	17.2%	6.8%	13.3%	31.4%		13.2%	28.9%	28.9%	#VALUE!
7	User-6	2023-01	15.1%	3.0%	0.9%	14.0%	45.4%		15.1%	44.0%	44.0%	#VALUE!
8	User-7	2023-01	26.9%	11.5%	9.1%	25.6%	71.0%		26.9%	70.9%	70.9%	#VALUE!
9	User-8	2023-01	29.1%	30.9%	15.5%	29.0%	100.0%		29.1%	100.0%	100.0%	#VALUE!
10	User-1	2023-02	7.1%	2.4%	0.1%	6.3%	106.3%
11	User-2	2023-02	6.5%	8.0%	9.2%	6.8%	113.1%
12	User-3	2023-02	9.6%	7.0%	1.3%	8.9%	122.0%
13	User-4	2023-02	11.0%	12.3%	7.1%	11.0%	133.0%
14	User-5	2023-02	10.4%	18.7%	16.9%	11.6%	144.6%
15	User-6	2023-02	11.1%	17.5%	14.1%	11.9%	156.5%
16	User-7	2023-02	19.3%	16.1%	12.2%	18.7%	175.1%
17	User-8	2023-02	24.8%	18.0%	38.9%	24.9%	200.0%
Sheet1

Cell Formulas
Range	Formula
I2:I9	I2	=SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1))
K2:K9	K2	=SUBTOTAL(9,OFFSET(I2,0,0,SEQUENCE(COUNT(I2#)),1))
L2:L9	L2	=SUBTOTAL(9,OFFSET(INDEX(SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1)),1),0,0,SEQUENCE(COUNT(SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1)))),1))
J2:J9	J2	=SUM($I$2:I2)
G2:G17	G2	=SUM($F$2:F2)
Dynamic array formulas.

 

[edvrdsdvskj]

The problem was solved using MMULT function.

Book1
	M
1	MMULT func.
2	0.0%
3	1.0%
4	3.7%
5	15.7%
6	28.9%
7	44.0%
8	70.9%
9	100.0%
Sheet1

Cell Formulas
Range	Formula
M2:M9	M2	=MMULT(--(SEQUENCE(ROWS(SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1))))>=SEQUENCE(,ROWS(SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1))))),SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1)))
Dynamic array formulas.

 

[KRice]

Here is another shorter option:

MrExcel_20231113.xlsx
	N
1	SCAN option
2	0
3	0.010152
4	0.03701
5	0.15736
6	0.289156
7	0.439686
8	0.708999
9	1
Sheet7

Cell Formulas
Range	Formula
N2:N9	N2	=SCAN(0,FILTER(C2:C17,B2:B17=I1),LAMBDA(a,v,v+a))
Dynamic array formulas.

 

[Fluff]

You could also shorten that formula using LET

=LET(f,SORT(FILTER($C$2:$C$17,$B$2:$B$17=I$1)),r,ROWS(f),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),f))

 

[Fluff]

@KRice that formula needs the sort, if you change I1 to 2023-02 you will get a different result from the OP.

 

[KRice]

That's a good point @Fluff ...thank you. That brings up an important question for @edvrdsdvskj ...based on your initial description, it appears you want to sort the S% values from small to large before performing the running total. Is that correct? If so, a corrected version is shown below. My earlier offering maintained the same order as the original source table, which appears to be in Username order (although I didn't explicitly sort by Username).

MrExcel_20231113.xlsx
	N
1	SCAN option
2	0
3	0.010152
4	0.03701
5	0.15736
6	0.289156
7	0.439686
8	0.708999
9	1
Sheet7

Cell Formulas
Range	Formula
N2:N9	N2	=SCAN(0,SORT(FILTER(C2:C17,B2:B17=I1)),LAMBDA(a,v,v+a))
Dynamic array formulas.

 

[edvrdsdvskj]

Yes, sorting is mandatory.

Thank you both, appreciate your help

 

[KRice]

Thanks for the clarification. We're happy to help.