SUMIF VBA ARRAY & DICTIONARY

[roykana]

Dear all master,
I want a fast code sumif vba array and dictionary because there are three hundred thousand records. I've also made the sumif vba array and dictionary code but it only works for 1 column and 1 criteria results.
Please help for the solution.
sheet name "OPS" with table name "OPS" with blue marking in the desired result in sheet "RECON"

SUMIF VBA ARRAY & DICTIONARY.xlsm
	A	B	C	D	E	F
1	ITEM NO	BOJ	CJR	M18	M07	MD2
2	01-17380098	36	10	1	5	2
3	01-17380171	6	10	6	5	4
4	01-17380185	12	10	7	5	1
5	01-17380186	10	11	12	13	14
6		64	41	26	28	21
OPS

Cell Formulas
Range	Formula
B6	B6	=SUBTOTAL(109,[BOJ])
C6	C6	=SUBTOTAL(109,[CJR])
D6	D6	=SUBTOTAL(109,[M18])
E6	E6	=SUBTOTAL(109,[M07])
F6	F6	=SUBTOTAL(109,[MD2])

sheet name "DBALL" with table name "DBALL" with yellow marking in the desired result in sheet "RECON"

SUMIF VBA ARRAY & DICTIONARY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	PNM	ITM	ITC	QTY	UNI	CIU	NOD	DPR	NCUR	QAB	GLB	DPT	DTS	CIA	UNB	DATE	SAC	DEPT	TRANS	GROUP	ITEM NO
2	GPPI11801001	TEST S 20525 DIGITALITY	T20525	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	BOJ	PURCHASE	In	01-17380098
3	GPPI11801002	TEST S 20551 DELIO	T20551	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M18	PURCHASE	In	01-17380171
4	GPPI11801003	TEST S 20526 DIGITALITY	T20526	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M07	PURCHASE	In	01-17380185
5	GPPI11801004	TEST S 20552 DELIO	T20552	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	MD2	PURCHASE	In	01-17380186
6	GPPI11801005	TEST S 20525 DIGITALITY	T20525	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	BOJ	SALES		01-17380098
7	GPPI11801006	TEST S 20551 DELIO	T20551	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M18	SALES		01-17380171
8	GPPI11801007	TEST S 20526 DIGITALITY	T20526	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M07	SALES		01-17380185
9	GPPI11801008	TEST S 20552 DELIO	T20552	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	MD2	SALES		01-17380186
10	GPPI11801009	TEST S 20525 DIGITALITY	T20525	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	BOJ	RET PURCH		01-17380098
11	GPPI11801010	TEST S 20551 DELIO	T20551	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M18	RET PURCH		01-17380171
12	GPPI11801011	TEST S 20526 DIGITALITY	T20526	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M07	RET PURCH		01-17380185
13	GPPI11801012	TEST S 20552 DELIO	T20552	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	MD2	RET PURCH		01-17380186
14	GPPI11801013	TEST S 20525 DIGITALITY	T20525	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	BOJ	RET SALES		01-17380098
15	GPPI11801014	TEST S 20551 DELIO	T20551	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M18	RET SALES		01-17380171
16	GPPI11801015	TEST S 20526 DIGITALITY	T20526	36	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	M07	RET SALES		01-17380185
17	GPPI11801016	TEST S 20552 DELIO	T20552	60	Pcs	89000	01	0								02/01/2018	A.01.01.001.063	MD2	RET SALES		01-17380186
DBALL

sheet name "IFGALL" with table name "IFGALL" with red marking in the desired result in sheet "RECON"

SUMIF VBA ARRAY & DICTIONARY.xlsm
	A	B	C	D	E
1	ITM	ITC	QOH	GROUP DEPT	ITEM NO
2	TEST S 20525 DIGITALITY	T20525	36	BOJ	01-17380098
3	TEST S 20551 DELIO	T20551	36	BOJ	01-17380171
4	TEST S 20526 DIGITALITY	T20526	36	BOJ	01-17380185
5	TEST S 20552 DELIO	T20552	36	BOJ	01-17380186
6	TEST S 20525 DIGITALITY	T20525	10	CJR	01-17380098
7	TEST S 20551 DELIO	T20551	10	CJR	01-17380171
8	TEST S 20526 DIGITALITY	T20526	10	CJR	01-17380185
9	TEST S 20552 DELIO	T20552	11	CJR	01-17380186
10	TEST S 20525 DIGITALITY	T20525	1	M18	01-17380098
11	TEST S 20551 DELIO	T20551	1	M18	01-17380171
12	TEST S 20526 DIGITALITY	T20526	1	M18	01-17380185
13	TEST S 20552 DELIO	T20552	2	M18	01-17380186
14	TEST S 20525 DIGITALITY	T20525	5	M07	01-17380098
15	TEST S 20551 DELIO	T20551	5	M07	01-17380171
16	TEST S 20526 DIGITALITY	T20526	5	M07	01-17380185
17	TEST S 20552 DELIO	T20552	5	M07	01-17380186
18	TEST S 20525 DIGITALITY	T20525	2	MD2	01-17380098
19	TEST S 20551 DELIO	T20551	2	MD2	01-17380171
20	TEST S 20526 DIGITALITY	T20526	2	MD2	01-17380185
21	TEST S 20552 DELIO	T20552	2	MD2	01-17380186
IFGALL

desired result

SUMIF VBA ARRAY & DICTIONARY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1		OPS					PURCHASE					SALES					RET SALES					RET PURCH					IFGALL					CALCULATION					CHECK
2	ITEM NO	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2	BOJ	CJR	M18	M07	MD2
3	01-17380098	36	10	1	5	2	36	0	0	0	0	36	0	0	0	0	36	0	0	0	0	36	0	0	0	0	36	10	1	5	2	36	10	1	5	2	s	s	s	s	s
4	01-17380171	6	10	6	5	4	0	0	60	0	0	0	0	60	0	0	0	0	60	0	0	0	0	60	0	0	36	10	1	5	2	36	10	1	5	2	s	s	s	s	s
5	01-17380185	12	10	7	5	1	0	0	0	36	0	0	0	0	36	0	0	0	0	36	0	0	0	0	36	0	36	10	1	5	2	36	10	1	5	2	s	s	s	s	s
6	01-17380186	10	11	12	13	14	0	0	0	0	60	0	0	0	0	60	0	0	0	0	60	0	0	0	0	60	36	11	2	5	2	36	10	1	5	2	s	ns	ns	s	s
7	TOTAL	64	41	26	28	21	36	0	60	36	60	36	0	60	36	60	36	0	60	36	60	36	0	60	36	60	144	41	5	20	8	144	40	4	20	8	s	ns	ns	s	s
RECON

Cell Formulas
Range	Formula
B3:B6	B3	=SUMIF(OPS[ITEM NO],$A3,OPS[BOJ])
C3:C6	C3	=SUMIF(OPS[ITEM NO],$A3,OPS[CJR])
D3:D6	D3	=SUMIF(OPS[ITEM NO],$A3,OPS[M18])
E3:E6	E3	=SUMIF(OPS[ITEM NO],$A3,OPS[M07])
F3:F6	F3	=SUMIF(OPS[ITEM NO],$A3,OPS[MD2])
G3:K6	G3	=SUMIFS(DBALL[QTY],DBALL[ITEM NO],RECON!$A3,DBALL[DEPT],RECON!G$2,DBALL[TRANS],RECON!$G$1)
L3:P6	L3	=SUMIFS(DBALL[QTY],DBALL[ITEM NO],RECON!$A3,DBALL[DEPT],RECON!L$2,DBALL[TRANS],RECON!$L$1)
Q3:U6	Q3	=SUMIFS(DBALL[QTY],DBALL[ITEM NO],RECON!$A3,DBALL[DEPT],RECON!Q$2,DBALL[TRANS],RECON!$Q$1)
V3:Z6	V3	=SUMIFS(DBALL[QTY],DBALL[ITEM NO],RECON!$A3,DBALL[DEPT],RECON!V$2,DBALL[TRANS],RECON!$V$1)
AA3:AE6	AA3	=SUMIFS(IFGALL[QOH],IFGALL[ITEM NO],RECON!$A3,IFGALL[GROUP DEPT],RECON!AA$2)
AF3:AJ6	AF3	=(B$3+G$3+Q$3)-(L$3+V$3)
AK3:AO7	AK3	=IF(AA3=AF3,"s","ns")
B7:AJ7	B7	=SUM(B3:B6)

Sub sumifarraydictionary()

   Dim Ary As Variant, Tmp As Variant
   Dim r As Long
   t = Timer
  
   Ary = Sheets("OPS").ListObjects("OPS").DataBodyRange.Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Array(Ary(r, 2), 0, 0)
         Else
            Tmp = .Item(Ary(r, 1))(0) + Ary(r, 2)
            .Item(Ary(r, 1)) = Array(Tmp, 0, 0)
         End If
      Next r
Sheets("RECON").Range("A3").Resize(.Count).Value = Application.Transpose(.Keys)
      Sheets("RECON").Range("B3").Resize(.Count, 1).Value = Application.Index(.items, 0)
   End With
      Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub

 

[DanteAmor]

I thought I gave you the answer in post #18, so I don't understand what you need the code in post #14 is "It's perfectly".

 

[roykana]

I thought I gave you the answer in post #18, so I don't understand what you need the code in post #14 is "It's perfectly".

@DanteAmor
Dear Mr. Dante Amor

I'm sorry I mean a different case example because this is just a little problem maybe you can help in this project. But if there are objections or disturbed then I apologize to you.

Thanks
roykana

 

[DanteAmor]

If they are different cases you must create a new thread. There you expose with examples the data you have and the desired results.

 

[roykana]

If they are different cases you must create a new thread. There you expose with examples the data you have and the desired results.

@DanteAmor
Thank you very much