Search code examples
excelexcel-formuladynamic-arrays

How to use SUM function with (new) dynamic arrays in Excel

Let's say 3 columns (A, B, C) are dynamic arrays and I want to create a fourth/final dynamic array formula that is the sum of these 3 columns for each row in column D. For clarity, I am looking for the row-by-row sum of each row in this final column.

This will work: =A2#+B2#+C2#

How can the same be accomplished by using the SUM function? The reason I ask is that this is easier to use on larger ranges of data.

The following gives a #REF! error: =SUM(A2:C2#)

Solution

  • New Edit:

    With the addition of BYROW and LAMBDA we can do this a little easier than my original answer below:

    =BYROW(A1#:C1#,LAMBDA(x,SUM(x)))

    The BYROW passes each row into the LAMBDA which does the SUM iteratively and returns an array:

    enter image description here

    Original Answer

    The problem is that SUM,MAX,MIN all allow arrays and do the whole on the full array. So we need to use something that uses arrays and spills individual results. That is what MMULT was built for.:

    =MMULT(A2#:C2#,TRANSPOSE(COLUMN(A2:C2)^0))

    enter image description here

    Just realized with the dynamic arrays we have SEQUENCE:

    =MMULT(A2#:C2#,SEQUENCE(COLUMNS(A2:C2),,1,0))