Search code examples
google-sheetsselectsumrangerow

GOOGLE SHEETS: Help needed to sum a range of cells across rows, where the range is controlled by a variable

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.

Solution

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

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