Sumproduct refers to named ranges in closed workbook

That won't work with dynamic named ranges.

Open the file. No alternatives, I'm afraid, unless you can use code in the source file to update a fixed named range each time it is saved.

I would have expected this to work:

In workbook Calculation, with workbook Source open, define

tbl Refers to =INDEX([Source.xls]Sheet1!$A:$A, 1):INDEX([Source.xls]Sheet1!$C:$C, MATCH("zzz", [Source.xls]Sheet1!$A:$A))

tblName refers to =INDEX(tbl, 0, 1)

tblMonth refers to =INDEX(tbl, 0, 2)

tblItems refers to =INDEX(tbl, 0, 3)

... and the formula to

=SUMPRODUCT((tblName=$A$2) * (tblMonth=B2), tblItems)

... and it does work, but only as long as Source is open.

I think the problem is with the range:index(range) syntax. Neither that, nor OFFSET, seem to be happy with closed workbooks.
More strangely, testing with:
='C:\[Source.xls]Sheet1'!$A$1:INDEX('C:\[Source.xls]Sheet1'!$A$1:$A$10,2)
works, but if you change the 2 to 3, a #REF! error is returned. :shrug:

That it fails with OFFSET I get.

Is it because because INDEX is interpreted as the Reference form, which returns a range reference, which invalidates its usage?

I can't reconcile that with your example that works, though.

That was my thought. I suspect the working example is the aberration.