Nov 08 2020 08:31 AM
Is it possible to have a list of excel spreadsheets stored in cells on one sheet:
Firstworkbook1.xls
Secondworkbook1.xls
Thirdworkbook1.xls
And then use the SUMPRODUCT() function to access each spreadsheet in turn to look for similar data without having to manually retype each worksheet name into the SUMPRODUCT() function.
I.E. I have maybe 200 or so spreadsheets...
Nov 09 2020 12:03 AM - edited Nov 09 2020 12:36 AM
Check this link for details,,, recently I've solved the similar issue,, what you have to do is,, replace AVERAGE with an appropriate SUMPRODUCT formula,, in case find issue, please drop a message or reply this post.
=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A4&"'!"&"D1:D25"),A10,INDIRECT("'"&A1:A4&"'!"&"E1:E25")))
Note: A1:A4 is range does have sheet name.
A10 is cell contents criteria.
OR
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetName&"'!"&"D1:D25"),A10,INDIRECT("'"&SheetNAme&"'!"&"E1:E25")))
Note: SheetName is Named Range,, for A1:A4 does have sheet name and A10 is cell contents criteria.
OR
SUMPRODUCT(SUMIFS(INDIRECT("'" & SheetName & "'!$D:$D"),INDIRECT("'" & SheetName & "'!$A:$A"),A10,INDIRECT("'" & Sheets & "'!$B:$B"),B10))
Note: A10 & B10 are cells contents criteria.
Adjust cell references and others as needed.