ARRAY Formula to sum two cells in the same row-CodePudding

I have the columns below (column I, J and K):

Qty1, Qty2, TotalQty

Where TotalQty for each row is simply the sum of the cells in columns I and J (Qty1 and Qty2).

How can I write an array formula that simply sums column I and J for each row?

I have the below but this just repeats the sum for the row the arrayformula is applied in to all cells below it:

ARRAYFORMULA(IF(A2:A="","",SUM($I2:$J2)))

What I want it to do is calculate each row's I,J sum separately.

CodePudding user response：

BYROW or MAP will do the trick. For this case, you can try MAP:

=MAP(A2:A,I2:I,J2:J,LAMBDA(ax,ix,jx,IF(ax="","",ix jx)))

CodePudding user response：

try in K2:

=ARRAYFORMULA(I2:I10 J2:J10)

with if it would be:

=ARRAYFORMULA(IF(A2:A="";;I2:I J2:J))

you can shorten it:

=INDEX(IF(A2:A="";;I2:I J2:J))

or use a different approach like:

=QUERY(I2:J; "select I J label I J''")

or some more advanced techniques like:

=BYROW(I2:J; LAMBDA(x; SUM(x)))

or the old way:

=INDEX(MMULT(I2:J*1; SEQUENCE(2; 1; 1; ))