Sum values with different criteria

[ATOP]

Hello,

Good people from excel forum i would have a question hot to simplify an equation im currently using.
Here is the situation:
We have 3 sheets
1. lets assume i have a data base in sheet 1 and there in column A i have code values (400, 300, 678, 402,...) which correspond to a certain value i want to extract in column B
2. lets assume i have this criteria range in sheet 2, as you can see below. For number 1 corresponding code values are 400, 300 and 678

A	B	C	D
1	400	300	678
2	402	403	404
3	406	5	6
4	407	1	2

3. in sheet 3 i want to extract values for number 1 from sheet 1 in column B, those are the values that have a code value in column A 400, 300 and 678 and i want to sum them. Is there any easier way to do that. I came up with this solution which is ok if u have small number of code values (3) but if u have 10 or more its imposibruuu.
for number 1:
+index(sheet1!B:B;Match(sheet2!B1;sheet1!A:A;0)
+index(sheet1!B:B;Match(sheet2!C1;sheet1!A:A;0)
+index(sheet1!B:B;Match(sheet2!D1;sheet1!A:A;0)
is there any chance to make some sort of an array that would in one equation sum all the values that correspond the codes for number 1?

I hope my question can be understood if not i can send workbook and explain it more in detail.

[hemesh]

It would be better to have a sample book

[JohnTopley]

Try

=SUMPRODUCT((Sheet1!$B$1:$B$1000)*((Sheet1!$A$1:$A$1000=Sheet2!$B1:$F1)))

B1:F1 contains your codes

[ATOP]

I think it works like a charm, i will do some more tests but so far it looks promising )