Sum the first N elements in a spreadsheet column

Question

I'm trying to sum the first N elements in a column, where N is a value in another cell. I've come up with this:

=sum(B1:indirect(concatenate("B", A10)))

Where A10 contains N. But I don't want to have to hardcode the column label in the concatenation, because I want it to continue to function if I move columns around. I tried using the column() function, but it returns a number, not a letter. I tried

=char(code("A") + column() - 1)

but this fails once the column gets to AA, and I'm at AK already. I could put a modulo-26 test in there, but I figure there has to be an easier way to get the column letter code, or to do a variable sum.

I'm using Apple Numbers, but the formulae in Numbers, Excel, LibreOffice, etc., all seem to be fairly similar, so if you have suggestions that work for other spreadsheets, please don't hold back.

Answer 1

Why not use the OFFSET function in combination with SUM? Anchor the offset in the first row of the column you want to sum, then use the value N you've put into another cell to set the height of the range reference that offset returns.

For example, say you want to sum column AK and you've specified the number of rows you want to sum in cell AZ1. The formula to do the summing would be:

    =SUM(OFFSET(AK$1,1,0,AZ1,1))

This is an array formula, so it would need to be entered using CTRL-SHIFT-ENTER.