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?
How do I calculate a sum based on a column number which is the value of another cell?
google spreadsheets;microsoft excel
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))Explanation: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.