Dynamic Array with Total and Subtotals (FILTERXML)

[DJunqueira]

Hi all, just sharing a cool new way to use FILTERXML() to generate dynamic array with total and subtotals all in on cell formula.

In cell D7:D11 you can manipulate the range that the formula is calculating the total and subtotals. Very cool.

Formula with Total and Subtotals in a dynamic array.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3						Columns
4						6	7	8	9					1	5	9	13
5				x> 0 ; x < 5										49	53	57	61
6				Condition		Matrix 5 x 4								65	69	73	77
7				1		1	5	9	13				Subtotal	115	127	139	151
8				0		17	21	25	29					17	21	25	29
9				0		33	37	41	45					33	37	41	45
10				1		49	53	57	61				Subtotal	50	58	66	74
11				1		65	69	73	77				Total	165	185	205	225
12
13
14						165	185	205	225
15
16						115	127	139	151
17
18						50	58	66	74
19
20						165	185	205	225
21
22
Totalização horizontal

Cell Formulas
Range	Formula
F4:I4	F4	=COLUMN(F5:I5)
M4:Q11	M4	=IFERROR(INDEX(FILTERXML("<F><d>"&TEXTJOIN("</d><d>",0,FILTER(E7:I11,D7:D11,"NA"),"Subtotal",TRANSPOSE(MMULT(TRANSPOSE($F$7#*D7:D11)*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0)),FILTER(E7:I11,D7:D11=0,"NA"),"Subtotal",TRANSPOSE(MMULT(TRANSPOSE($F$7#*ABS(D7:D11-1))*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0)),"Total",TRANSPOSE(MMULT(TRANSPOSE($F$7#)*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0)))&"</d></F>","//d"),SEQUENCE(ROWS(FILTER(E7:I11,D7:D11,"NA"))+1+ROWS(FILTER(E7:I11,D7:D11=0,"NA"))+2,5)),0)
F7:I11	F7	=SEQUENCE(5,4,1,4)
F14	F14	=SUMPRODUCT($F$7#*(COLUMN($F$7:$I$7)^0)*(--(F4#=COLUMN())))
G14	G14	=SUMPRODUCT($F$7#*(COLUMN($F$7:$I$7)^0)*(--(F4#=COLUMN())))
H14	H14	=SUMPRODUCT($F$7#*(COLUMN($F$7:$I$7)^0)*(--(F4#=COLUMN())))
I14	I14	=SUMPRODUCT($F$7#*(COLUMN($F$7:$I$7)^0)*(--(F4#=COLUMN())))
F16:I16	F16	=TRANSPOSE(MMULT(TRANSPOSE($F$7#*D7:D11)*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0))
F18:I18	F18	=TRANSPOSE(MMULT(TRANSPOSE($F$7#*ABS(D7:D11-1))*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0))
F20:I20	F20	=TRANSPOSE(MMULT(TRANSPOSE($F$7#)*ROW($F$7:$F$10)^0,ROW($F$7:$F$11)^0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:Q14	Expression	=$M4="Total"	text	NO
M4:Q14	Cell Value	<>0	text	NO
M4:Q14	Expression	=$M4="Subtotal"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D7:D11	List	1;0

 

[ISY]

Hi

Hello
I found an alternative formula for Excel 365

=LET(C,A7:A11,L,ROWS(C),S,SEQUENCE(L)^0,T,{1,1,1,1,1},R,B7:F11,K,IFERROR(INDEX(FILTERXML(""<t><s>""&TEXTJOIN(""</s><s>"",FALSE,FILTER(R,C=1,""""),TRANSPOSE(MMULT(TRANSPOSE(R*C)*T,S)),FILTER(R,C=0,""""),TRANSPOSE(MMULT(TRANSPOSE(R*NOT(C))*T,S)),TRANSPOSE(MMULT(TRANSPOSE(R)*T,S)))&""</s></t>"",""//s""),SEQUENCE(L+3,5)),""""),CHOOSE({1\2\2\2\2},IF(K=0,IF(SEQUENCE(L+3)<L+3,""????????"",""?????""),""""),K))

 

[DJunqueira]

Unfortunately I'm not in the Office Insider program, my Windows is still in the 7 version.
Tks any way, I saved the formula for when MS make it available for all.