Forum Discussion

ACB-COL's avatar
ACB-COL
Copper Contributor
Nov 08, 2020

Multiple excel spreadsheets

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 ...
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Nov 09, 2020

ACB-COL 

 

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.  

 

  • Here is few sample formula:

 

=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.

Resources