Just to throw out another way it could be done

=SUM(YTD*(B10="YTD"),QTR1*(B10="QTR1"))

array entered using Cntl+Shift+Enter

"Frank Hayes" wrote:

I am trying to use the Sumproduct function on a named range using the
Indirect function. When the named range is a fixed range, it works fine.
When I try to make the range dynamic using Offset, Excel returns "#REF!"
instead of the value.

Here is a simplified illustration of what I am trying to do:

On Sheet1, A2 to A5 contains Sales Region names North, South, East, and
West. Row 1 column B to M is Month Name, Jan to December. Sales are shown
for each Sales Region in each Month in the appropriate cell.

Cell B8 contains a number that corresponds to the current month. In this
example, I am interested in data through March, so the value is 3.

Cell B10 contains a dropdown that is based on a list of 2 possible values.
The first value is "QTR1" and the second value is "YTD".

"QTR1" is a named range for the sales in Jan, Feb, and Mar defined as :
=Sheet1!$B$2:$D$5
"YTD" is a named range defined as : =OFFSET(Sheet1!$B$2,0,0,4,Sheet1!$B$8)

Cell B12 contains the formula: =SUMPRODUCT(INDIRECT(B10))

When I choose "QTR1" from the dropdown, the function returns the number.
However, when I choose "YTD" from the dropdown, Excel returns "#REF!". Can
someone point me in the right direction?

Thanks

Frank Hayes