Excel: #CALC! error (Nested Array) when using MAP, BYROW, BYCOL, MAKEARRAY and LAMBDA functions

Question

I'm often encountering very frustrating #CALC! errors when using LAMBDA inside a MAP, BYROW, BYCOL, or MAKEARRAY function. This is very similar to the following question but I found that example and solution too long to adapt it to my own situation: Excel: #CALC! error (Nested Array) when using MAP functions for counting interval overlaps

Below is a contrived but minimal example that illustrates my problem:

=LET(
    nums, SEQUENCE(25),
    rs, {10; 20},
    cs, {1, 2, 3},
    MAKEARRAY(
        ROWS(rs),
        COLUMNS(cs),
        LAMBDA(i, j,
            LET(
                r, INDEX(rs, i),
                c, INDEX(cs, j),
                SUMIFS(nums, nums, r + c)
            )
        )
    )
)

Answer 1

Thanks to the minimal example I think I was able to get to the bottom of this.

The problem seems to be actually due to the SUMIFS function rather than anything else. I noticed that the documentation for SUMIFS mentions that it takes ranges (rather than an arrays) as arguments and this is what I think is behind the errors.

Unfortunately I often need to do these kinds of cross-tabulations.

EDIT: Thanks to @(David Leal) for pointing out in a comment below that my initial SUMPRODUCT formulation can be replaced with the following SUM based one:

=LET(
    nums, SEQUENCE(25),
    rs, {10; 20},
    cs, {1, 2, 3},
    MAKEARRAY(
        ROWS(rs),
        COLUMNS(cs),
        LAMBDA(i, j,
            LET(
                r, INDEX(rs, i),
                c, INDEX(cs, j),
                SUM(nums * (nums = r + c))
            )
        )
    )
)

@David Leal further provided the following link that goes into more detail about the RACON Excel Functions. This is worth reading! Thank you David!