I need to sum a range of cells across rows, but I need to be able to specify the amount with a variable.

For example. If i write 5 in cell B1, I want to sum range A1:A5. If i write 10 in cell B1, I want to sum range A1:A10. If i write 20 in cell B1, I want to sum range A1:A20.

And so on.

Does anyone know a formula for this?

Kind regards.

I tried writing( in cell B1) =SUM(A1:A(1 B1)). This didn't work at all, instead a =NAME? appeared.

CodePudding user response：

You may use INDIRECT to set a range by joining text, like this:

=SUM(INDIRECT("A1:A"&B1))

CodePudding user response：

This formula will also work:

=SUM(BYROW(SEQUENCE(B1),LAMBDA(ROW,INDEX(A1:A,ROW))))

SEQUENCE() return an array of rows count up from 1 by default.

BYROW() can use an array as row reference and apply a LAMBDA() function to each row.

INDEX() return the CELL the matches a given INDEX of ROW and COL.

SUM sum up every value of a range (array).

CodePudding user response：

also possibility:

=SUM(A1:INDEX(A:A; B1))

or:

=SUMPRODUCT(A1:INDEX(A:A; B1))