Sumproduct Indirect Named Dynamic Range using Offset


Frank Hayes

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 :
"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?


Frank Hayes


Would this be OK in B12:


Assumes QTR1, QTR2 are fixed ranges.


Frank Hayes

That does work and thank you for the suggestion. I think I will play with
Indirect a bit and see if there is anyway to fool it with a Dynamic named



Don't spend any time with INDIRECT and dynamic ranges: it simply doesn't work.

I am sure Biff would have corrected me if it did as he is extremely
knowledgeable (more so than I) about Excel. He has given you an alternative


Just to throw out another way it could be done


array entered using Cntl+Shift+Enter

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question