Sumproduct not working with a dropdown menu list

[sahuja]

I have a worksheet where I have a drop down list on cell A1 which contains regions (like West, Central, East). I have the regions defined as named ranges in my worksheet (which is another tab in the same sheet).

I am trying to use this sumproduct formula instead of using multiple if's but it keeps giving me a value# error.
=SUMPRODUCT((Program="Common")*(Task=D6)*(CELL("contents",A1)))

so if west is selected in cell A1, the cell function in the formula is returning "West" in double quotes in the sumproduct formula, which is not being accepted as an array in the sumproduct formula.

Can some one tell me how I can make it return as west and not "west" to make sumproduct recognise as an array.

[Jonmo1]

Try

=SUMPRODUCT((Program="Common")*(Task=D6)*INDIRECT(A1))

[Tony Valko]

Maybe this...

=SUMIFS(INDIRECT(A1),Program,"Common",Task,D6)

If "West" is defined using functions like OFFSET then this will not work.

[sahuja]

it works...thanks a lot

[Tony Valko]

Can't tell who you're replying to but in any case...

You're welcome. We appreciate the feedback!

[Jonmo1]

Your welcome.

Using sumifs is probably better anyway