Addition/Summarization of Multiple Dynamic Arrays into Month Year Table-CodePudding

As a follow up to my previous question

CodePudding user response：

For a solution that works without the 'newer' functions such as BYROW, BYCOL, LAMBDA, LET, TAKE and DROP, the following formula works too:

=MMULT(
    SEQUENCE(1,COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0),
    (YEAR(INDEX($E$3:$N$4,INT(SEQUENCE(COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0)/COLUMNS($E$3:$N$4)) 1,MOD(SEQUENCE(COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0),COLUMNS($E$3:$N$4)) 1))=$C$11#)
        *(MONTH(INDEX($E$3:$N$4,INT(SEQUENCE(COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0)/COLUMNS($E$3:$N$4)) 1,MOD(SEQUENCE(COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0),COLUMNS($E$3:$N$4)) 1))=$B12)
        *INDEX($D$3:$D$4,INT(SEQUENCE(COLUMNS($E$3:$N$4)*ROWS($E$3:$N$4),1,0)/COLUMNS($E$3:$N$4)) 1)
)

Although there is quite a lot of redundant calculation in there since LET is not available.

What this is doing essentially is illustrated in this simplified (non-functional) formula:

=MMULT(
    vector_of_ones,
    (years_bonds=years_matrix)*(months_bonds=month_matrix)*(interest_payments)
)

This solution can be pasted in C12 and then dragged down.

Note that the input array $E$3:$N$4 for the payment schedule and $D$3:$D$4 for the interest payments is hard written into the formula. Meaning, these would have to be extended in the formula manually or replaced by an OFFSET() or FILTER() array that adjust automatically. It is also possible to already allow for a larger array here, e.g. $E$3:$N$10 and $D$3:$D$10 as long as those extra rows are empty. A 3rd solution would be to create a dynamic arrays out of the input arrays elsewhere and only referring to those intermediary dynamic arrays without the need to make any changes to the final formula.

See screenshot for an example.

CodePudding user response：

Following your logic you could use the following in C12:

=SUM(
     BYROW($D$3:$P$6,
           LAMBDA(r,
                  LET(
                      values,TAKE(r,,1),
                      dates,DROP(r,,1),
IF(OR((MONTH(dates)=$B12)*(YEAR(dates)=C$11)),values,0)))))

It uses BYROW to "loop" through the Interest Payment values and the Payment Schedule date values. LET is used to separate the values (first column values from range by row) from the dates (all other columns,except the first from the range by row). Then your IF-function repeats itself with the values and dates row by row. Since this would spill per row I wrapped it in SUM, so the sum of each rows result is the outcome.

You still have to drag the formula to the other cells, like your own formula, but if you append a row of data to your payments data table, it's a matter of changing $D$3:$P$6 to $D$3:$P$7 and row 7 would be included as well.