Sumproduct with indirect on a dynamic range?

[bakubaku]

Hello All,

I have the name of a dynamic range (D_Conv3) in one cell (A7)
the formula for the range is as follows :OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1)
the range contains only numbers

I'm trying to find a sumproduct formula which would fetch and use that range name

So the natural way for me to go would be to build something like:
=SUMPRODUCT((INDIRECT(A7))
but since i'm only getting a #REF error with this method, I'm guessing the SUMPRODUCT won't work as long as you're mixing it with the INDIRECT on a dynamic range

is there a way to bypass the problem?
either with the sumproduct or by using something else?

thanks in advance

 

[jasonb75]

That will work, the error is in the defention of the range

:OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1) is a text string, not a formula,

It should be

=OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1)

If that's not giving the range you're looking for, what should the correct range be?

 

[bakubaku]

sorry, there's no error, I miscopied it when writing the post,but the range definition in my file is indeed
=OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1)

and it's still giving me the #REF

 

[jasonb75]

What is in N1:N6? Are any of those cells merged?

What is in the rest of column N?

 

[T. Valko]

That will work, the error is in the defention of the range

:OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1) is a text string, not a formula,

It should be

=OFFSET(Data!$N$7;0;0;COUNTA(Data!$N:$N)-5;1)

If that's not giving the range you're looking for, what should the correct range be?

INDIRECT will not work when the dynamic named range is defined using functions like OFFSET.

 

[jasonb75]

INDIRECT will not work when the dynamic named range is defined using functions like OFFSET.

I stand corrected

 

[bakubaku]

That's what I was afraid of

Here is a link to download the test file
http://www.filedropper.com/testindirect

 

[T. Valko]

That's what I was afraid of

Here is a link to download the test file
http://www.filedropper.com/testindirect

Not sure what you're trying to do in that test file.

You can try something like this:

=SUMPRODUCT(CHOOSE(MATCH(A7,Data!L$5:S$5,0),D_Conv1,D_Conv2,D_Conv3,D_Conv4))

 

[bakubaku]

Perfect!
It does exactly the trick
thanks a lot!

 

[T. Valko]

Perfect!
It does exactly the trick
thanks a lot!

Good deal. Thanks for the feedback!

Just a note...

I'm guessing that the "final" formula will be more complicated than:

=SUMPRODUCT(CHOOSE(MATCH(A7,Data!L$5:S$5,0),D_Conv1,D_Conv2,D_Conv3,D_Conv4))

Which is simply performing a SUM on the named range. If an unconditional sum is all that you want to do then you can use the SUM function:

=SUM(CHOOSE(MATCH(A7,Data!L$5:S$5,0),D_Conv1,D_Conv2,D_Conv3,D_Conv4))