ASUBTOT

[Xlambda]

ASUBTOT array subtotal, inserts sum subtotal rows, every n-th rows of an array. calls ARUNTOT , AUNSTACK , ASTACK
- with cl argument we can choose what columns will be summed {2,4,5} or simple ,3
- ctx - column index where "SubTotal" text string will be placed, if we want to change this text, in the formula is the first variable after LET.
Other function used on the minisheet to check the results APIVOT

=LAMBDA(a,n,cl,ctx,
    LET(tx,"SubTotal",k,INT(ABS(n)),ct,MAX(1,ctx),r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(c),
       au,AUNSTACK(IF(a="","",a),k),at,ARUNTOT(au,),t,INDEX(at,k,),m,MOD(SEQUENCE(,COLUMNS(t))-1,c)+1,
       xm,XMATCH(m,cl),x,IF(ISNA(xm),"",t),y,IF(m=ct,tx,x),u,IF(SEQUENCE(k+1)=k+1,y,au),as,ASTACK(u,c),z,MMULT(--(as<>""),s^0),
       ca,AND(ISNUMBER(XMATCH(cl,s)),ISNUMBER(XMATCH(ct,s))),
       IF(NOT(ca),"check clm values",FILTER(as,z))
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	sample any data					=ASUBTOT(A2:D16,6,{3,4},)					=ASUBTOT(A2:D16,4,{1,2,3},4)					=ASUBTOT(A2:D16,10,4,2)
2	e	3	3	d	n=6	e	3	3	d	n=4	e	3	3	d	n=10	e	3	3	d
3	3	h	h	f	cl={3,4}	3	h	h	f	cl={1,2,3}	3	h	h	f	cl=4	3	h	h	f
4	t	5	f	1	ctx ,	t	5	f	1	ctx=4	t	5	f	1	ctx=2	t	5	f	1
5	y		2	y		y		2	y		y		2	y		y		2	y
6	u	7	4			u	7	4			3	8	5	SubTotal		u	7	4
7	i	v	5	8		i	v	5	8		u	7	4			i	v	5	8
8	o	9	z	r		SubTotal		14	9		i	v	5	8		o	9	z	r
9	6	g	8	t		o	9	z	r		o	9	z	r		6	g	8	t
10		1	4	3		6	g	8	t		6	g	8	t			1	4	3
11	e	s		u			1	4	3		6	16	17	SubTotal		e	s		u
12	7	3	8	i		e	s		u			1	4	3			SubTotal		12
13	t	d	9	5		7	3	8	i		e	s		u		7	3	8	i
14	2	5	p	w		t	d	9	5		7	3	8	i		t	d	9	5
15	u	6	4	9		SubTotal		29	8		t	d	9	5		2	5	p	w
16	i	8	5	r		2	5	p	w		7	4	21	SubTotal		u	6	4	9
17						u	6	4	9		2	5	p	w		i	8	5	r
18						i	8	5	r		u	6	4	9			SubTotal		14
19						SubTotal		9	9		i	8	5	r
20											2	19	9	SubTotal
21																other functions on minisheet			APIVOT
22	sample							H24	=ASUBTOT(A24:F43,4,{4,5},2)						check
23	Year/Qtr	Prod	unit price	nr.u sold	tot	Obs		Year/Qtr	Prod	unit price	nr.u sold	tot	Obs		=APIVOT(A24:F43,1,2,5,)
24	20 Qtr 1	P1	5	8	40	d		20 Qtr 1	P1	5	8	40	d		(1\2) 5 vf=0	P1	P2	P3	P4	Grand Total
25	20 Qtr 1	P2	3	7	21	f		20 Qtr 1	P2	3	7	21	f		20 Qtr 1	40	21	18	48	127
26	20 Qtr 1	P3	6	3	18	v		20 Qtr 1	P3	6	3	18	v		20 Qtr 2	45	12	36	16	109
27	20 Qtr 1	P4	8	6	48	b		20 Qtr 1	P4	8	6	48	b		20 Qtr 3	25	30	72	56	183
28	20 Qtr 2	P1	5	9	45	t			SubTotal		24	127			20 Qtr 4	45	12	36	40	133
29	20 Qtr 2	P2	3	4	12	j		20 Qtr 2	P1	5	9	45	t		21 Qtr 1	45	9	42	40	136
30	20 Qtr 2	P3	6	6	36	d		20 Qtr 2	P2	3	4	12	j		Grand Total	200	84	204	200	688
31	20 Qtr 2	P4	8	2	16	t		20 Qtr 2	P3	6	6	36	d
32	20 Qtr 3	P1	5	5	25	u		20 Qtr 2	P4	8	2	16	t		=APIVOT(A24:F43,1,2,4,)
33	20 Qtr 3	P2	3	10	30	y			SubTotal		21	109			(1\2) 4 vf=0	P1	P2	P3	P4	Grand Total
34	20 Qtr 3	P3	6	12	72	s		20 Qtr 3	P1	5	5	25	u		20 Qtr 1	8	7	3	6	24
35	20 Qtr 3	P4	8	7	56	x		20 Qtr 3	P2	3	10	30	y		20 Qtr 2	9	4	6	2	21
36	20 Qtr 4	P1	5	9	45	j		20 Qtr 3	P3	6	12	72	s		20 Qtr 3	5	10	12	7	34
37	20 Qtr 4	P2	3	4	12	d		20 Qtr 3	P4	8	7	56	x		20 Qtr 4	9	4	6	5	24
38	20 Qtr 4	P3	6	6	36	u			SubTotal		34	183			21 Qtr 1	9	3	7	5	24
39	20 Qtr 4	P4	8	5	40	r		20 Qtr 4	P1	5	9	45	j		Grand Total	40	28	34	25	127
40	21 Qtr 1	P1	5	9	45	d		20 Qtr 4	P2	3	4	12	d
41	21 Qtr 1	P2	3	3	9	a		20 Qtr 4	P3	6	6	36	u
42	21 Qtr 1	P3	6	7	42	f		20 Qtr 4	P4	8	5	40	r
43	21 Qtr 1	P4	8	5	40	s			SubTotal		24	133
44								21 Qtr 1	P1	5	9	45	d
45								21 Qtr 1	P2	3	3	9	a
46								21 Qtr 1	P3	6	7	42	f
47								21 Qtr 1	P4	8	5	40	s
48									SubTotal		24	136
49
ASUBTOT post

Cell Formulas
Range	Formula
F1,O32,O23,P1,K1	F1	=FORMULATEXT(F2)
F2:I19	F2	=ASUBTOT(A2:D16,6,{3,4},)
K2:N20	K2	=ASUBTOT(A2:D16,4,{1,2,3},4)
P2:S18	P2	=ASUBTOT(A2:D16,10,4,2)
I22	I22	=FORMULATEXT(H24)
H24:M48	H24	=ASUBTOT(A24:F43,4,{4,5},2)
O24:T30	O24	=APIVOT(A24:F43,1,2,5,)
O33:T39	O33	=APIVOT(A24:F43,1,2,4,)
E24:E43	E24	=D24*C24
Dynamic array formulas.