excel - Sumproduct in different workbook and mulriple sheet -


hi wanna ask excel program. first have 2 workbooks, "stock" , "160601".

in workbook "160601" :

sheet 1 : xyz 1

sheet 2 : xyz 4

in workbook "stock" :

i want xyz 5

can use sumproduct formula ?

thank you

assuming worksheets follows:

workbook: 160601 sheet: sheet1

enter image description here

workbook: 160601 sheet: sheet2

enter image description here

you'll need have names of sheets of 160601 workbook in sheet1 of stock workbook. below i've used cell e4 , e5 write names of sheet of 160601 workbook.
write following formula in sheet1 of stock workbook:

=sumproduct(sumif(indirect("'[16061.xlsx]"&$e$4:$e$5&"'!a1:a2"),a1,indirect("'[16061.xlsx]"&$e$4:$e$5&"'!b1:b2")))

workbook: stock sheet: sheet1

enter image description here


edit# 1
______________________________________________________________________________

when workbook closed indirect , sumif not work. alternatively, can use following formula (looks little ugly):

=sum(sumproduct(--('c:\test_folder\test\[16061.xlsx]sheet1'!a1:a2=a1),'c:\test_folder\test\[16061.xlsx]sheet1'!b1:b2),sumproduct(--('c:\test_folder\test\[16061.xlsx]sheet2'!a1:a2=a1),'c:\test_folder\test\[16061.xlsx]sheet2'!b1:b2))

-