enter image description here

I want to summarize in line 8, all the values in A2:A6. I want the function to get the column number from another cell (B8). Same for line 9. I want A9 to summarize b2:b6.

In other words: How to add column number argument to sum function in Google Sheets?


This is a job for the INDIRECT function. For the sum in A8, use

=SUM(INDIRECT("R2C" & B8 & ":R5C" & B8; FALSE))

Likewise, for the sum in A9, use

=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: R2C1:R5C1.

The 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 INDIRECT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.