Forum Discussion

SubhasisB's avatar
SubhasisB
Copper Contributor
Jan 04, 2021
Solved

Sumproduct with Indirect

Hello Friends, I have two excel file, the detail file is having monthwise sheet where department wise excpenses is given for budget, rolling plan and actual period. In summary want to fetch monthwis...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 04, 2021

    SubhasisB 

    In your case that could be like

    =SUMPRODUCT(
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$B$2:$B$13")=$B3)*
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$1:$E$1")=C$1)*
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$2:$E$13"))
)

    Please not, INDIRECT() doesn't work with closed file, thus full path is not required - you shall to keep Details opened.

SergeiBaklan's avatar
SergeiBaklan
MVP
Jan 04, 2021

SubhasisB 

In your case that could be like

=SUMPRODUCT(
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$B$2:$B$13")=$B3)*
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$1:$E$1")=C$1)*
  (INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$2:$E$13"))
)

Please not, INDIRECT() doesn't work with closed file, thus full path is not required - you shall to keep Details opened.

SubhasisB's avatar
SubhasisB
Copper Contributor
Jan 08, 2021
Thank You very much for the reply.

Resources