Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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:


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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .