Combining Sumproduct & Indirect 1

[ScorchedLemonade]

Hi,

I'm trying to combine the above to functions to allow the insertion of extra data to be multiplied and summed.

Thus the formula I want to use would look a bit like this (if it worked):

=SUMPRODUCT(INDIRECT("B3:"&CHAR(65+$A$1)&"3&quot,INDIRECT("B"&ROW()&":"&CHAR(65+$A$1)&ROW()))

but this gives a #VALUE! error.
A1 contains the array formula

{=MAX(IF(ISBLANK(3:3),0,COLUMN(3:3)))-1}

and this does give the correct value.
The plan was that CHAR(65+$A$1) will then give the letter of the column needed in the SUMPRODUCT arrays.
Thus, when it is 4 for instance, the first array would be B3:E3 and the second would be Bn:En depending on the row number n (the formula would be copied down).
(The first array is always from row 3 as it is a weighting array, but the second array will vary from row to row giving calculations for a time series. The insertion of extra columns would correspond to putting in data for further companies.)

Is it possible to do something like this?

Thanks,

Richard.

 

[GlennUK]

Your formula is absolutely correct except for one tiny thing. Instead of using ROW() use INDEX(TEXT(ROW(),"0&quot,1), and it should work.

Glenn.

 

[ScorchedLemonade]

Thanks - that worked as you said.

Richard.

 

[GlennUK]

My pleasure

Always glad to help.


Cheers, Glenn.