Formula:
= BYROW(B3#:D3#, LAMBDA(row, SUM(row)))Syntax
Both the BYROW() and LAMBDA() syntax is important here. BYROW() uses a specific array, and passes each row of that array into a LAMDA() function, which iteratively sums the array.
= BYROW([array], LAMBDA([row])) LAMDA() can be used to define multiple parameters and use them in one larger calculation. In this case we just want to perform a Sum on each row. The row being the parameter and calculation being sum.
= LAMBDA([parameter], [calculation]))
= LAMBDA(row, SUM(row))Combining both functions with an array, in this case B3#:D3# (the # notation in a cell reference means that there is a dynamic array held in cell B3# and refers to the entire spilled range), you get the following:
= BYROW(B3#:D3#, LAMBDA(row, SUM(row)))Explanation
To sum up a set of dynamic arrays, we can employ the use of the BYROW() function which in turn uses the LAMBDA() function to run a user-defined function across every single row.
In the example above, B3, C3, and D3, all house a dynamic range that spills down 5 rows. This is done using the SEQUENCE() function. The dynamic array's contents do not matter, only that it your formula in each row is spilling its contents downward.
Here you can see that by using the cell reference B3#, Excel pulls the entire spilled range of the dynamic array held in cell B3, rather than the specific cell value of B3 "1".
By using the same methodology across all of our ranges using = B3#:D3#, Excel is actually creating a new dynamic array across all the arrays held in B3:D3. A dynamic array of dynamic arrays, which we want to use as the final array in the BYROW() function.
So now we know what the [array] piece of the function is, and need to tell the function what calculation to perform on each row. Since we want to sum each row, we can simply use:
LAMBDA(row, SUM(row)))Here we are creating a new parameter "row" in the first step, and then summing each total row, as passed in by the BYROW() function. BYROW() is giving each row of the specified array to the LAMBDA() which sums it all up.
Note that the parameter name here doesn't matter. It will work just as well being named "x" vs "row"
Putting it all together gives us:
= BYROW(B3#:D3#, LAMBDA(row, SUM(row)))