Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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