Time sensitive - need help fixing this formula

Hi

Hoping someone can help, I need to gather data from multiple worksheets on multiple workbooks

The below gives me #REF error

=SUMPRODUCT(INDIRECT("'"&$C$5&"'[JAMIE SHEET METRO.xlsx]!'""'c$12:c$25"),$C$9:$C$22,INDIRECT("'"&$C$5&"'[JAMIE SHEET METRO.xlsx]!J$12:J$25"))

Cell C5 refers to tab names which can be changed using a drop down list
c12:c25 is criteria
j12:j25 is data needed

Is my formula structured wrong or is a different one needed?

Any feedback and possible suggestions/solutions would be much appreciated




Mod note : Cross posted here
https://www.mrexcel.com/forum/excel-...t-formula.html