This is a job for the
INDIRECT function. For the sum in
=SUM(INDIRECT("R2C" & B8 & ":R5C" & B8; FALSE))
Likewise, for the sum in
=SUM(INDIRECT("R2C" & B9 & ":R5C" & B9; FALSE))
INDIRECT lets you construct cell addresses from dynamic input, e.g. from other cells. It supports both A1 notation (as in
A2), and R1C1 notation (like
R2C1, for "row 2, column 1").
In this case, we are constructing a range string:
1 is the dynamic part, which is being picked up from cell
B8. So all we have to do is concatenate the string from the parts
R2C+ [contents of B8] +
:R5C+ [contents of B8]. The
& is the string concatenation operator in Google Sheets.
See the example spreadsheet I set up, and the documentation for