pull & sum values across sheets based on headers

[KalilMe]

hi

I would sum values for columns SALE,RET for sheets FRU,FES ,FN,SS and put the values in sheet COLLECTION into columns SALE,RET . the matching data in sheets FRU,FES ,FN,SS depend on column B should match with column B,C,D i in sheet COLLECTION .If they are matched then pull values and merge & sum the duplicate items across multiple sheets . with ignore unmatched items .
I have data almost 1400 rows across sheets .

COLLECTION (1).xlsm
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS	12	-
3	2	VEG TOMATO AA	12	12
4	3	FR BANANA TT	200	10
5	4	FR APPLE LL	100	20
6	5	FR PEAR NN	10	-
7	6	FR BANANA QQ	20	-
FRU

COLLECTION (1).xlsm
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS	20	-
3	2	VEG TOMATO AA	22	12
4	3	FR BANANA TT	12	10
5	4	FR APPLE LL	12	2
6	5	FR BANANA QQ	12	-
FEG

COLLECTION (1).xlsm
	A	B	C
1	ITEM	BR	SALE
2	1	FR BANANA QQ	10
3	2	VEG TOMATO AA	20
4	3	FR BANANA TT	40
5	4	FR APPLE LL	50
6	5	FR PEAR NN	5
FN

COLLECTION (1).xlsm
	A	B	C
1	ITEM	BR	RET
2	1	FR BANANA TT	5
3	2	FR APPLE LL	2
4	3	FR PEAR NN	3
SS

the result should just be in columns E,F

COLLECTION (1).xlsm
	A	B	C	D	E	F	G
1	ITEM	BR	TY	OR	SALE	RET	BALANCE
2	1	FR	BANANA	TT	252	25	227
3	2	FR	APPLE	LL	162	42	120
4	3	FR	PEAR	NN	15	3	12
5	4	FR	BANANA	QQ	42		42
6	5	VEG	TOMATO	SS	20		20
7	6	VEG	TOMATO	AA	42	12	30
8	7	VEG	ONION	AA1			-
9	8	VEG	ONION	AA2			-
10	9	VEG	ONION	AA3			-
COLLECTION

Cell Formulas
Range	Formula
G2:G10	G2	=E2-F2

 

[steve the fish]

Why does item 5 on your results sum to 20? Anyway maybe something like:

=SUMPRODUCT(SUMIFS(INDIRECT({"FRU","FEG","FN"}&"!C:C"),INDIRECT({"FRU","FEG","FN"}&"!B:B"),B2&" "&C2&" "&D2))

 

[KalilMe]

Why does item 5 on your results sum to 20?

sorry ! my bad
should be 32 .
as I said

I have data almost 1400 rows across sheets .

then should be by vba . I no know if you're able to achieve it by vba . even if you can't or nobody else provide me macro , then I will use your formula .
it works very well for column sale , but how mod for column RET ? becuase there is different loctation .

 

[KalilMe]

another option