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

Question

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#)

Vow

What is the reason of using # in formula? =SUM(A2:C2) shall work as you need. – Recalescence

Using # converts it into a Dynamic Array formula. This changes its nature completely as now it AUTOMATICALLY adjusts its length to match the size of your data WITHOUT you needing to manually copy your formula every time the length of the data set changes – Vow

=SUM(A2#:C2#) – Kaifeng

@ScottCraner - that doesn't sum row-by-row. – Strident

It works for me. @Strident – Kaifeng

And spills? What are your formulas in A to C? – Strident

Ah, I thought they wanted the total, not individual @Strident – Kaifeng

I do think it's confusing since OP mentioned =A2#+B2#+C2#, while # refers to a spilled range, this would also make me believe OP is after the total. However SUM(A:C) isn't used which then makes me believe he actually is after a row-by-row calculation – Joettejoey

I have edited question to make it clear that I am looking for the row-by-row result – Vow

Answer 1

---

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:

---

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))

---

Just realized with the dynamic arrays we have SEQUENCE:

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

Kaifeng

It aint pretty but it works. I was however hoping for something a little more elegant/natural... – Vow

@Vow 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. I may be wrong but I doubt there will be anything more elegant/natural – Kaifeng

@ScottCraner This is excellent! I used it to replace SUMIF(S) on rows. Something like: =MMULT(A2#:C2#,TRANSPOSE(--(A1:C1<myNumber))) or whatever other condition(s) are needed – Alinaaline

Answer 2

Try this for sums per column (assuming A1# is the dynamic range with the source data):

=SUBTOTAL(9,OFFSET(A1#,0,SEQUENCE(1,COLUMNS(A1#))-1,ROWS(A1#),1))

Just change the first argument of the SUBTOTAL function to use any of the aggregation functions available (min, max, average, etc.). With some tweaks, this can be made to work for row totals.

Amersfoort

Answer 3

When I tried Scott Craner's formula above I received a VALUE# error, possibly related to the fact that I was testing it on data columns of different dynamic lengths.

Even though the expression A2#:C2# returns a matrix of width 3 columns, and height of whichever of the three columns has the most rows (filling in zeros for any blank cells), MMULT didn't seem to like that expression and resulting matrix as the first argument. However I found that I was able to modify it as follows to make it work:

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

Capitally

Answer 4

Absolute legend. I've been trying to work this out. Thank you so much. I have never used MMULT before, only SUMPRODUCT for similar problems. My problem was to dynamically sum up the last few columns in a spill table and I was able to adapt your solution.

In terms of the question above, if the three spill columns were one spill table with rows and columns in the one spill range it would be:

=MMULT(A2#,SEQUENCE(ROWS(A2#)),SEQUENCE(COLUMNS(A2#)),,1,0))

Georg