dynamic cell reference

[rnl0085]

i'm trying to get a function to work that uses a dynamic cell reference:

sumproduct($A$1:$A$20,$B$1:$B$20)
where the 1 (in cell C1) and 20 (in cell C2) i have dynamic:
sumproduct(indirect("$A$"&C1&":$A$"&C2&",$B$"&C1&":$B$"&C2)
this doesn't work however..even trying only with a single argument in the sum function within the indirect doesn't work (then it tries to take the sum of just to single cells (a1 and a20, sum(x:x) with x the values of a1 and a20 instead of sum(a1:a20)

 

[Aladin Akyurek]

i'm trying to get a function to work that uses a dynamic cell reference:

sumproduct($A$1:$A$20,$B$1:$B$20)
where the 1 (in cell C1) and 20 (in cell C2) i have dynamic:
sumproduct(indirect("$A$"&C1&":$A$"&C2&",$B$"&C1&":$B$"&C2)
this doesn't work however..even trying only with a single argument in the sum function within the indirect doesn't work (then it tries to take the sum of just to single cells (a1 and a20, sum(x:x) with x the values of a1 and a20 instead of sum(a1:a20)

Try...

=SUMPRODUCT(OFFSET(INDEX(A:A,$C$1),0,0,$C$2),OFFSET(INDEX(B:B,$C$1),0,0,$C$2))