Help with Functions in Excel 2013

[sree_kalpax]

Hi,
I have 2 sheets in excel (sheet1 & sheet2), in sheet1 I have a column of data in column A (say A1:A4), and in sheet2 I have a matrix of data from A1:F6, the column A data of sheet1 is a subset of column A data in sheet2, now what I want to do is...check whether any cell in column A of sheet1 exists in column of sheet2 and if so, I would like to fix that row in sheet2 and make a selective addition of that row ie., to illustrate with an example,

sheet1
A B C D E
1 $ $$ $$$ $$$$
2 abc
3 def
4 ghi
5 xyz

sheet2
A B C D E F
1 $ $$ $$$ $$$$ $ Total
2 abc 1 22 3 0 2 28
3 chi 5 3 0 2 2 12
4 def 35 3 0 0 1 39
5 uvw 8 9 10 11 0 38
6 mno 1 2 1 1 0 5

Final Result
So in sheet 1 B2, I need the sum of "B2+F2" from sheet2.

Regards,
Sreeder

 

[gaz_chops]

Can you post your actual formula, can't see why it should fail!

 

[sree_kalpax]

This is the formula I am using

=SUMPRODUCT((Sheet1!$A$9:$A$2000=$C6)*(Sheet1!$B$7:$CO$7=L$5),Sheet1!$B$9:$H$2000)

 

[gaz_chops]

Your ranges look wrong, try=SUMPRODUCT((Sheet1!$A$9:$A$2000=$C6)*(Sheet1!$B$7:$CO$7=L$5),Sheet1!$B$9:$CO$2000)

 

[sree_kalpax]

Aah....that's so bad of me! Thanks for pointing it out. It works now!

 

[gaz_chops]

Lol no problem, the 3rd part of the formula has to be same number of row as the 1st part & same number of columns as the 2nd part. Gaz