row reference

[mike allen]

i would like to sum a series of numbers in columnB and put in cell C1.
which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in
other words, i want to sum the numbers from B13 to B29 in this case. i know
i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be
changing frequently. next time A1 and A2 might be 9 and 36, respectively,
thus summing cells B9:B36. i do not want to manually change the formula in
C1 every time i change A1 and A2. i also do not want to create a macro. any
thoughts? thanks, mike allen

 

[Peo Sjoblom]

A couple of ways

=SUM(INDEX(B:B,A1):INDEX(B:B,A2))

more functions but not volatile

=SUM(INDIRECT("B"&A1&":B"&A2))

and you can also use OFFSET

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Arvi Laanemets]

Hi

=SUM(INDIRECT("B" & A1 & ":B" & A2))
or
=SUM(OFFSET(B1,A1-1,,A2-A1+1,1))