excelexcel-formulaexcel-2019

Excel, sum of X number of cells under current cell

I'm trying to calculate the sum of X (2) cells under the current cell. In this example, I want A1 to be the sum of X cells under the current cell (A1); A1 = A2 + A3; A1 = 4 + 7; A1 = 11.

A1 should be the sum of the 2 cells under A1

In this other example, I want A1 to be the sum of X (3) cells under current cell (A1); A1 = A2 + A3 + A4; A1 = 4 + 7 + 8; A1 = 19.

A1 should be the sum of the 3 cells under A1

To simplify this, I' have created the examples with A1, but I would like to use the formula on any cell and add the value of X cells under the current cell.

I've tried with OFFSET without success.

=SUM(OFFSET(A1:A10,1,0,C1,1))

It works but I would like to change A1:A10 for a more generic version.

Any advice? Thanks

Solution

  • I think this is what you had in mind:

    =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,0,C1,1))

    or "old style":

    =SUM(INDEX(A:A,ROW()+1):INDEX(A:A,ROW()+C1))

    or if you really hate anyone who has to maintain it in future:

    =LET(
    α, ROW(),
    β, LAMBDA(χ, ROW() + χ),
    γ, LAMBDA(ν, SUM(OFFSET(INDEX(A:A, α), 1, 0, ν, 1))),
    δ, γ(C1),
    δ
)

    enter image description here