How To Append Vector Of Row Sums Using Let Function In Excel

Solution for How To Append Vector Of Row Sums Using Let Function In Excel
is Given Below:

My question is two parts:

  1. Is there a way to return a vector of the row sums of a data frame using the LET function?
  2. Can one return this column appended to the original data frame in the same function?

For example, given the below data, I want to return the full data frame along with the column c(6, 15, 24).

enter image description here

I guess the first order of business is getting each individual row, but I have struggled so far. I’ve tried a couple things like =LET(x, $A$1:$C$3, INDEX(x, ROW($A$1:$A$3), 0)) or =LET(x, $A$1:$C$3, y, ROW($A$1:$A$3), INDEX(x, y,y)) but no dice. Any tricks? Maybe the CHOOSE function could come in handy?

=LET( data, A1:C3,TRANSPOSE(MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0))))
enter image description here
By multiplying all the values of one row with 1 per row using MMULT you can avoid SUM and INDEX. The result is a spilled sum result already that can be transposed.


Not sure if you’re u wanted to include the data set in the result; in that case you could use: =LET( data, A1:C3,drows,ROWS(data),sum,TRANSPOSE(MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0))),IF(SEQUENCE(drows+1)<=drows,data,sum))
enter image description here

I just figured you would want it to be added as a column rather than a row (makes more sense too): =LET( data, A1:C3,dcolumns,COLUMNS(data),sum,MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0)),IF(SEQUENCE(1,dcolumns+1)<=dcolumns,data,sum))
enter image description here

Aesthetic improvement showing blanks as blanks and zeroes as zeroes in the spill of the original range:
=LET( data, A1:C3,dcolumns,COLUMNS(data),sum,MMULT(--data,SEQUENCE(COLUMNS(data),1,1,0)),IF(SEQUENCE(1,dcolumns+1)<=dcolumns,IF(LEN(data)<>0,data,""),sum)