Using Indirect with Sumifs

I'm having trouble with the Indirect() function. I put the following formula:

=SUMIFS(INDIRECT(D6),Evaluation_Metric,"="&B$9,Calculation,"="&C$9)

D6 = Week_1_Jan-11
B9 = LOSS_RESERVE
C9 = ITD

So I'm trying to ask, in the 1st Week in January, what is the total Loss Reserve for the Incurred To Date calculation?

The formula returns "#REF!". Why is it returning "#REF!"? Is there a rule that the text string it is evaluating cannot have a number in it? I'm coming to the end of very long spreadsheet building process and this not working could potentially cause a huge headache.

When I pull up the "Functions Arguments" window (click fx next to formula box). If I have INDIRECT(D6) in the "Sum_Range", the evaluation to the left says "Volatile". If I type, Week_1_Jan-11 it gives me a blank. If I actually go and select the desired cells, it automatically inputs a variation of the Defined Names (Week_1_Jan_11) but doesn't return any results.

Can someone please help?

Is Week_1_Jan-11 supposed to be a named range? The hyphen character is not valid.

Yup.

A name has to start with a letter or underscore, and the balance can be a combinations of letters, numbers, and underscores.

No, you can use it directly in the formula:

=SUMIFS(Week_1_Jan_11, Evaluation_Metric, B$9, Calculation, C$9)

You've lost me.

You cannot use Indirect with a dynamic range, if that's what you mean.

Originally Posted by JohnGault82

I just changed all the Defined Names so that the hyphen has been removed. And I used the indirect and it is coming up with #value!.

If you received #VALUE! rather than #REF! with:

=SUMIFS(INDIRECT(D6), Evaluation_Metric, B$9, Calculation, C$9)

then the implication is that the above ranges are not of the same dimension rather than defined name as set in D6 being dynamic, to test:

=ROWS(INDIRECT(D6))
=ROWS(Evaluation_Metric)
=ROWS(Calculation)

should all be the same - if they are repeat the above but with COLUMNS

It might be worth posting back with the RefersTo constructs of your Defined Names and/or better yet with a sample file - I suspect you can avoid the INDIRECT requirement altogether.