Can't pass a range to a lambda using Let and Makearray functions

Question

Referring back to this question, I wish to create a running total in an array, but starting afresh in each row of the array

1	2	3	4
5	6	7	8

resulting in

1	3	6	10
5	11	18	26

One solution is to use Makearray and this works fine:

=MAKEARRAY(
    2,
    4,
    LAMBDA(r, c,
        SUM(
            INDEX(sheet1!A1:D2, r, 1) : INDEX(sheet1!A1:D2, r, c)
        )
    )
)

However I now wish to write this more generally using a Let statement:

=LET(
    range, Sheet1!A1:D2,
    MAKEARRAY(
        rows(range),
        Columns(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

but it results in

This very similar formula works correctly (it isn't supposed to solve the original problem, but is just included to test whether a range can be passed into a lambda inside a Let statement):

=LET(
    range, Sheet1!A1:D2,
    SCAN(0, range, LAMBDA(a, c, a + c + INDEX(range, 1, 1)))
)

The same code also works passing range as a parameter into a named function in Google Sheets.

Defining range as a named range also works.

Using an enclosing Lambda instead of Let like this has the same issue in Excel:

=LAMBDA(range,
    MAKEARRAY(
        ROWS(range),
        COLUMNS(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

I've named it TestLambda and called it as =TestLambda(A1:D2)

But this works in Google Sheets:

=LAMBDA(range,
    MAKEARRAY(
        ROWS(range),
        COLUMNS(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)(A1:D2)

Can there be a specific issue with Makearray or is there another reason why this fails?

Answer 1

The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like:

=LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c))))))

This would resemble the construct of your inital formula. However, in the linked question you have mentioned that you'd like to use SCAN() in combination with BYROW(). You have noticed that nesting these lambda helper functions will result in an error. I just wanted to show you that it is possible with just a single SCAN() function:

=SCAN(0,A1:D2,LAMBDA(a,b,IF(COLUMN(b)=1,b,a+b)))

Or even less verbose:

=SCAN(0,A1:D2,LAMBDA(a,b,a*(COLUMN(b)<>1)+b))

I have been puzzling a bit and found that we could compare the row/column of the 'b' variable inside the lambda structure. Something I haven't been aware of prior to....now. It does feel a bit glitchy but now we can actually use some sort of variation to the BYROW() helper inside SCAN() and do these calculation for each row of the intial array seperately.