Need to replace this with a SUMPRODUCT function - help please

[colinheslop1984]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]LEEDS[/TD]
[/TR]
[TR]
[TD]DRESS[/TD]
[TD]=SUMIF(INDIRECT("'[JAMIE SHEET LEEDS.xlsx]"&$C$5&"'!C$12:C$25"),$C9,INDIRECT("'[JAMIE SHEET LEEDS.xlsx]"&$C$5&"'!J$12:J$25"))[/TD]
[/TR]
[TR]
[TD]DENIM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody></tbody>[/TABLE]

Where JAMIE SHEET LEEDS is the data source which has multiple worksheets.

The INDIRECT function is being used because C5 has a filter to select week number (which represents the worksheets of the data source)

C12:C25 is a criteria (dress, denim, etc as above)

J12:J25 the value I need to sum.

I need this info to display on my master document without it needing to be open.

Please help.

 

[DRSteele]

It is my understanding that function INDIRECT requires the linked workbook to be open.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF ! error value.​