For the example provided you could use the OFFSET function.

=SUM(OFFSET(A1,0,0,1,$L2))
Assumes Column L is fixed.