Add Dynamic Sum with Dynamic Array when using SUMIFS

[LearnMeExcel]

Hi all
i solve the first Problem for Dynamic name With Filter by @Fluff
on the Same Data
i want to Add Dynamic Sum with Same Dynamic Formula
this is my data

Invoice Number​	Date​	Company Name​	Amount​	Currency​	Status​
V - 141​	15/01/2021​	XYZ​	1153.132​	USD​	Not Paid​
V - 157​	17/01/2021​	FHG​	4491.818​	USD​	Paid​
V - 158​	26/01/2021​	ZJC​	6076.498​	USD​	Paid​
V - 167​	16/02/2021​	FHG​	1105.557​	USD​	Paid​
V - 173​	17/02/2021​	XYZ​	7338.599​	USD​	Paid​
V - 184​	21/02/2021​	CDE​	2176.351​	USD​	Paid​
V - 194​	06/03/2021​	THC​	7663.309​	USD​	Paid​
V - 204​	07/03/2021​	ZJC​	2955.909​	USD​	Paid​
V - 252​	23/03/2021​	MFC​	4456.528​	USD​	Paid​
V - 278​	28/03/2021​	ABC​	3432.406​	USD​	Paid​
V - 306​	28/03/2021​	MFC​	3381.936​	USD​	Not Paid​
V - 325​	31/03/2021​	JBR​	7563.771​	USD​	Paid​
V - 326​	31/03/2021​	FHG​	4094.144​	USD​	Paid​
V - 327​	19/04/2021​	EFG​	7828.469​	USD​	Paid​
V - 330​	21/04/2021​	MFC​	6602.376​	USD​	Not Paid​
V - 342​	23/04/2021​	XYZ​	2979.044​	USD​	Paid​
V - 374​	25/04/2021​	FHG​	7932.121​	USD​	Paid​
V - 387​	05/05/2021​	FHG​	3621.784​	USD​	Paid​
V - 397​	06/05/2021​	FHG​	4714.177​	USD​	Paid​
V - 415​	27/05/2021​	JBR​	4942.058​	USD​	Paid​
V - 433​	27/05/2021​	CDE​	7999.256​	USD​	Paid​
V - 434​	31/05/2021​	ABC​	2743.007​	USD​	Paid​
V - 478​	31/05/2021​	THC​	1122.415​	USD​	Paid​
V - 523​	02/06/2021​	EFG​	4673.887​	USD​	Paid​
V - 524​	16/06/2021​	JBR​	2126.972​	USD​	Paid​
V - 531​	21/06/2021​	XYZ​	8576.646​	USD​	Not Paid​
V - 544​	25/06/2021​	MFC​	8452.265​	USD​	Not Paid​
V - 616​	27/06/2021​	FHG​	6481.728​	USD​	Paid​
V - 637​	07/07/2021​	CDE​	4694.205​	USD​	Not Paid​
V - 671​	08/07/2021​	MFC​	3248.498​	USD​	Not Paid​
V - 677​	24/07/2021​	ZJC​	2986.056​	USD​	Paid​
V - 703​	07/08/2021​	THC​	1342.146​	USD​	Paid​
V - 759​	11/09/2021​	JBR​	4226.128​	USD​	Paid​
V - 770​	12/09/2021​	ZJC​	1949.528​	USD​	Paid​
V - 815​	14/09/2021​	MFC​	5725.759​	USD​	Not Paid​
V - 846​	28/09/2021​	FHG​	8204.793​	USD​	Paid​
V - 861​	29/09/2021​	MFC​	6108.563​	USD​	Paid​
V - 887​	01/10/2021​	FHG​	2274.161​	USD​	Paid​
V - 903​	10/10/2021​	ZJC​	5287.774​	USD​	Paid​
V - 910​	12/10/2021​	ZJC​	7230.193​	USD​	Not Paid​
V - 913​	12/10/2021​	ABC​	1109.748​	USD​	Paid​
V - 926​	29/10/2021​	CDE​	2849.42​	USD​	Paid​
V - 929​	01/11/2021​	FHG​	4117.116​	USD​	Paid​
V - 934​	04/11/2021​	MFC​	4752.744​	USD​	Paid​
V - 937​	07/11/2021​	XYZ​	4851.978​	USD​	Paid​
V - 938​	14/11/2021​	CDE​	7279.049​	USD​	Not Paid​
V - 965​	27/11/2021​	XYZ​	2766.666​	USD​	Paid​
V - 966​	28/11/2021​	JBR​	4601.909​	USD​	Paid​
V - 985​	06/12/2021​	CDE​	1092.158​	USD​	Paid​
V - 986​	15/12/2021​	EFG​	1015.34​	USD​	Not Paid​

my Unique list

Not Paid​

CDE​

EFG​

MFC​

XYZ​

ZJC​

By Formula
=SORT(UNIQUE(FILTER(INDEX(Invoice_data,,3),INDEX(Invoice_data,,6)="Not Paid")))
And i used SUMIFS

Not Paid​	Amount​
CDE​	11973.25​	=IF(I2#="",SUM($J$1:J1),SUMIFS(INDEX(Invoice_data,0,4),INDEX(Invoice_data,0,3),I2#,INDEX(Invoice_data,0,6),"Not Paid"))​
EFG​	1015.34​
MFC​	27410.83​
XYZ​	9729.778​
ZJC​	7230.193​

but Sum doesn't work to give me after last value the total for all

Can we do that ???

 

[Fluff]

In I2 use

=LET(s,SORT(UNIQUE(FILTER(INDEX(Invoice_data,,3),INDEX(Invoice_data,,6)="Not Paid"))),IFERROR(INDEX(s,SEQUENCE(ROWS(s)+1)),""))

and in J2

=LET(s,SUMIFS(INDEX(Invoice_data,0,4),INDEX(Invoice_data,0,3),I2#,INDEX(Invoice_data,0,6),"Not Paid"),IF(I2#="",SUM(s),s))

 

[LearnMeExcel]

Many Thanks @Fluff
amazing Solution

 

[LearnMeExcel]

Just To understand, why i get zero as results if i use this function in Column J

=IF(I2#="Total",SUMIFS(INDEX(invoice_data,,4),INDEX(invoice_data,,3),I2#,INDEX(invoice_data,,6),"Not Paid"),SUM($J$1:J1))

Not Paid	Amount
CDE	0.00​
EFG	0.00​
MFC	0.00​
XYZ	0.00​
Yahya	0.00​
ZJC	0.00​
Total	0.00​

 

[Fluff]

Firstly it looks as though you have the if statement the wrong way round & secondly you cannot use SUM($J$1:J1) in a spilled array as it will only look at J$1:J1

 

[LearnMeExcel]

Firstly it looks as though you have the if statement the wrong way round & secondly you cannot use SUM($J$1:J1) in a spilled array as it will only look at J$1:J1

Yes, I noticed the error after I added the reply.
and as understand Excel can't Evaluate SUM($J$1:J1) to change the range through dynamic Array.

 

[Fluff]

as understand Excel can't Evaluate SUM($J$1:J1) to change the range through dynamic Array.

That's right, it will only ever look at J1

 

[LearnMeExcel]

That's right, it will only ever look at J1

Thank you @Fluff for your help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[LearnMeExcel]

another case if it is possible
if i want Report Vertical and Horizontal like (Pivot Table ) and Sum Vertical and Horizontal and Grand Total if it is Possible, i try this Formulas

is it possible by Dynamic array

MrExcel Site.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Invoice Number	Date	Company Name	Amount	Currency	Status			Not Paid	CNY	RUB	USD	HTotal
2	V - 141	15/01/2021	Abc	1153.132	USD	Not Paid			Abc	7,230.19	0.00	1,153.13	0.00
3	V - 157	17/01/2021	FHG	4491.818	EUR	Paid			CDE	7,279.05	4,694.21	0.00	0.00
4	V - 158	26/01/2021	ZJC	6076.498	CNY	Paid			EFG	1,015.34	0.00	0.00	0.00
5	V - 167	16/02/2021	FHG	1105.557	JPY	Paid			MFC	3,381.94	17,550.90	15,054.64	0.00
6	V - 173	17/02/2021	XYZ	7338.599	CNY	Paid			VTotal	57,359.40	57,359.40	57,359.40	57,359.40
7	V - 184	21/02/2021	CDE	2176.351	CNY	Paid
8	V - 194	06/03/2021	THC	7663.309	RUB	Paid
9	V - 204	07/03/2021	ZJC	2955.909	JPY	Paid
10	V - 252	23/03/2021	MFC	4456.528	JPY	Paid
11	V - 278	28/03/2021	ABC	3432.406	RUB	Paid
12	V - 306	28/03/2021	MFC	3381.936	CNY	Not Paid
13	V - 325	31/03/2021	JBR	7563.771	USD	Paid
14	V - 326	31/03/2021	FHG	4094.144	CNY	Paid
15	V - 327	19/04/2021	EFG	7828.469	RUB	Paid
16	V - 330	21/04/2021	MFC	6602.376	USD	Not Paid
17	V - 342	23/04/2021	XYZ	2979.044	RUB	Paid
18	V - 374	25/04/2021	FHG	7932.121	USD	Paid
19	V - 387	05/05/2021	FHG	3621.784	CNY	Paid
20	V - 397	06/05/2021	FHG	4714.177	RUB	Paid
21	V - 415	27/05/2021	JBR	4942.058	JPY	Paid
22	V - 433	27/05/2021	CDE	7999.256	CNY	Paid
23	V - 434	31/05/2021	ABC	2743.007	USD	Paid
24	V - 478	31/05/2021	THC	1122.415	JPY	Paid
25	V - 523	02/06/2021	EFG	4673.887	USD	Paid
26	V - 524	16/06/2021	JBR	2126.972	JPY	Paid
27	V - 531	21/06/2021	MFC	8576.646	RUB	Not Paid
28	V - 544	25/06/2021	MFC	8452.265	USD	Not Paid
29	V - 616	27/06/2021	FHG	6481.728	CNY	Paid
30	V - 637	07/07/2021	CDE	4694.205	RUB	Not Paid
31	V - 671	08/07/2021	MFC	3248.498	RUB	Not Paid
32	V - 677	24/07/2021	ZJC	2986.056	EUR	Paid
33	V - 703	07/08/2021	THC	1342.146	RUB	Paid
34	V - 759	11/09/2021	JBR	4226.128	CNY	Paid
35	V - 770	12/09/2021	ZJC	1949.528	EUR	Paid
36	V - 815	14/09/2021	MFC	5725.759	RUB	Not Paid
37	V - 846	28/09/2021	FHG	8204.793	JPY	Paid
38	V - 861	29/09/2021	MFC	6108.563	USD	Paid
39	V - 887	01/10/2021	FHG	2274.161	EUR	Paid
40	V - 903	10/10/2021	ZJC	5287.774	EUR	Paid
41	V - 910	12/10/2021	Abc	7230.193	CNY	Not Paid
42	V - 913	12/10/2021	ABC	1109.748	RUB	Paid
43	V - 926	29/10/2021	CDE	2849.42	JPY	Paid
44	V - 929	01/11/2021	FHG	4117.116	USD	Paid
45	V - 934	04/11/2021	MFC	4752.744	EUR	Paid
46	V - 937	07/11/2021	XYZ	4851.978	CNY	Paid
47	V - 938	14/11/2021	CDE	7279.049	CNY	Not Paid
48	V - 965	27/11/2021	XYZ	2766.666	JPY	Paid
49	V - 966	28/11/2021	JBR	4601.909	USD	Paid
50	V - 985	06/12/2021	CDE	1092.158	JPY	Paid
51	V - 986	15/12/2021	EFG	1015.34	CNY	Not Paid
iDB-VH

Cell Formulas
Range	Formula
J1:M1	J1	=LET(v,TRANSPOSE(SORT(UNIQUE(FILTER(INDEX(invoice_data,,5),INDEX(invoice_data,,6)="Not Paid")))),IFERROR(INDEX(v,SEQUENCE(,COLUMNS(v)+1)),"HTotal"))
I2:I6	I2	=LET(s,SORT(UNIQUE(FILTER(INDEX(invoice_data,,3),INDEX(invoice_data,,6)="Not Paid"))),IFERROR(INDEX(s,SEQUENCE(ROWS(s)+1)),"VTotal"))
J2:M6	J2	=LET(s,SUMIFS(INDEX(invoice_data,0,4),INDEX(invoice_data,0,3),I2#,INDEX(invoice_data,0,6),"Not Paid",INDEX(invoice_data,0,5),J1#),V,SUMIFS(INDEX(invoice_data,0,4),INDEX(invoice_data,0,3),I2#,INDEX(invoice_data,0,6),"Not Paid"),H,SUMIFS(INDEX(invoice_data,0,4),INDEX(invoice_data,0,6),"Not Paid",INDEX(invoice_data,0,5),J1#),IF(I2#="VTotal",SUM(V),s))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'iDB-VH'!_FilterDatabase	='iDB-VH'!$A$1:$F$51	J1:J2, I2
invoice_data	=OFFSET('iDB-VH'!$A$1,1,0,COUNTA('iDB-VH'!$A:$A)-1,6)	J1:J2, I2