Count / Sum data from two different sheets

[sdm2211]

I have two worksheets

Worksheet "ORDER"

	A	B	C	D	E
1	Part Name	Order 1	Order 2	Order 3	Order 4
2	Nut Bolts	1		1
3	Pistons	2			2
4	Hammers	1	2

<tbody>
</tbody>

Worksheet "PRODUCTION"

	A	B	C	D
1	Part Name	Stock	Order	To be produced
2	Pistons	1
3	Hammers	2
4	Nut Bolts	1

<tbody>
</tbody>

I want to sum in "Order" column above from data to be consider from worksheet "ORDER"

for e.g. C2 should reflect result "4" taking into consideration data from worksheet Order (where Order 1 = 2 and Order 4 = 2)

Please help....I am stuck here badly

Regards!!!
Sandeep

 

[Aladin Akyurek]

In C2 of PRODUCTION control+shift+enter, not just enter, and copy down:

=SUM(IF(Sheet1!$A$2:$A$4=$A$2,$B$2:$E$4))

 

[kvsrinivasamurthy]

In C2 then drag down

=SUM(INDEX(ORDER!$C$2:$F$4,MATCH(Sheet2!$A2,ORDER!$A$2:$A$4,0),))

 

[Guru008]

Try this
change range accordingly
=SUMPRODUCT(--(B4:B6=B12)*(C4:F6))

 

[sdm2211]

In C2 then drag down

=SUM(INDEX(ORDER!$C$2:$F$4,MATCH(Sheet2!$A2,ORDER!$A$2:$A$4,0),))

Thanks a lot Murthy...It worked as per my expectation