SUMIFS using a named range with an address in a cell using INDIRECT

[Peter Davison]

I have a named range "PC" in a cell in workbook where the formula is going looking up the sum range and the criteria range in another workbook. I have the Criteria in Cell C17 of the workbook where the formula is. I have the formula below which returns the word VALUE and I cannot see why. I broke it up to see if each part was working and I think it all is.
Any help on what I am doing wrong would be really helpful.
Thanks

=SUMIFS(INDIRECT("'"&"["&"Breakfast Foods Combined Raw Data.xlsx"&"]"&"Performance Data'!"&$C$15),'[Breakfast Foods Combined Raw Data.xlsx]Performance Data'!$A$2:$A$100000,"="&C17)

 

[C Moore]

Your ranges have to be the same size. Sounds like PC (assuming its C15) is variable size but you hard coded $A$2:$A$100000.

 

[C Moore]

Have you used Evaluate Formula on the formula tab to step through?

 

[Peter Davison]

Hi, yes I stepped through the formula and it just gets to the end and shows Value.
I've just made them all a named range and it looks like it is working okay now.
Thank you very much for your help.