Sumproduct the column with criteria in a row

[fayez_MrExcel]

How can i sumproduct a particular column that matches a criteria. i have a heading with Product Name, code1, code2, code3 in sheet1. And in sheet2 (where i will put the formula), i have a heading Code and amount. I want to sum all Product name (example all books) that is under the code1

 

[arthurbr]

Perhaps something like = SUM(INDEX(A1:D100,,MATCH("code1",a1:a4,0)) where your table is A1:D100

 

[fayez_MrExcel]

_______Code1 | Code2 | Code3
Books___ 5_____ 3______ 7
Pencil___ 2_____ 6______ 5
Books___ 6_____ 8______


Sheet 2
Code | Amount
Code1 11 ( that is the sum of books under code1)

Sorry for the example table

 

[arthurbr]

Adapt my formula to your table