Struggling with indirect sumifs

Looks like you've got a reference to A:1 when you meant a reference to A:A in the second term of the SUMIFS there (the first Criteria Range).

And the Fourth input (the second Criteria Range) is being given a row when the other ranges are columns -- and since you're just telling it to work on the WHOLE ENTIRE RANGE instead of a selection, the fact that there are more rows than columns might also be a problem -- it would throw the #VALUE error.

Also you've got it wrapped with a SUMPRODUCT that isn't really doing anything.

Thus what you probably want is:

=IFERROR(SUMIFS(INDIRECT("'"&Store_Table[Store]&"'!C:C"), INDIRECT("'"&Store_Table[Store]&"'!$A:$A"), $Y$5, INDIRECT("'"&Store_Table[Store]&"'!$B:$B"), $Y$10, INDIRECT("'"&Store_Table[Store]&"'!$A:$A"), $D$15), "ERROR")

Note that the B:B might not be the right range.

Also you're referencing A:A in two ranges, so if Y5 <> D15, then the function will always return zero.