Search code examples
excelexcel-formuladynamic-arrays

Apply an independent formula till the range of Dynamic Array Formula

In DataSet1 the first two columns come from a FILTER() function in Excel. The third column is a sum of corresponding row from Dataset2.

I want the "Total Nums" column also to be dynamic and extended for the number of rows in the filter function. I tried

=IF(CHOOSECOLS(G4#,1)<>"",K4+L4,0)

However, that just adds K4 and L4 and repeats the same number for rest of the rows. Any help would be appreciated.

enter image description here

Solution

  • This can also be accomplished using ROW() function as well. That said =ROW(G4#)-3 returns an array starting from {1;..;last_row} and expands/resizes automatically based on the adjacent array G4#.

    enter image description here

    Formula used in cell I4

    =LET(α,ROW(G4#)-3,INDEX(K4:K18,α)+INDEX(L4:L18,α))

    Also one another alternative way using BYROW() without LAMBDA() construct and this presently works with MS365 for Beta Channel Users.

    enter image description here

    =BYROW(INDEX(K4:L18,ROW(G4#)-3,{1,2}),SUM)

    Or,

    =BYROW(CHOOSEROWS(K4:L18,SEQUENCE(ROWS(G4#))),SUM)