Count / Sum data from two different sheets

[sdm2211]

I have two worksheets

Worksheet "ORDER"

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part Name[/TD]
[TD]Order 1[/TD]
[TD]Order 2[/TD]
[TD]Order 3[/TD]
[TD]Order 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Nut Bolts[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pistons[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Hammers[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








Worksheet "PRODUCTION"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part Name[/TD]
[TD]Stock[/TD]
[TD]Order[/TD]
[TD]To be produced[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pistons[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hammers[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Nut Bolts[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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