SOLVED

# Sumproduct with Indirect

Copper Contributor

# 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 monthwise data from detail file, to change the month want to use the indirect function rather than changing month by Ctrl+H.

Thanks and Regards,
Subhasis.
3 Replies
best response confirmed by SubhasisB (Copper Contributor)
Solution

# Re: Sumproduct with Indirect

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.

# Re: Sumproduct with Indirect

Thank You very much for the reply.

# Re: Sumproduct with Indirect

@SubhasisB , you are welcome

1 best response

Accepted Solutions
best response confirmed by SubhasisB (Copper Contributor)
Solution

# Re: Sumproduct with Indirect

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.