-1

Would like your help to solve the problem as file attached.

https://docs.google.com/spreadsheets/d/1E3KTOYHuYIJy9uQKsvlXwbCpwL6cU1IHAQ-Yirb-AfQ/edit#gid=0

I would like each column to sum automatically (using ArrayFormula) when there is new name added and amount added in each row of the same column.

5
  • 2
    That is a Google sheet not Excel. Which of the two are you using? The formula may be different. If it is Google sheets then this belongs on WebApps.Stackexchange.com. If it is Excel then post the data in a mark down text table in the question that shows the data and the expected results.
    – Scott Craner
    Mar 7 at 15:21
  • Using Google Sheets one could use =ARRAYFORMULA(BYCOL(B7:E13,LAMBDA(x,SUM(x)))+B4:E4) and with Excel --> just remove the ARRAYFORMULA() Mar 7 at 15:24
  • And what have you tried so far? This is not a free scripting site. We help those who make efforts to help themselves.
    – gns100
    Mar 7 at 15:24
  • Consider providing input and expected output as plain text table in the question. Click here to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files.
    – TheMaster
    Mar 8 at 9:36
  • thanks for help. you have help me a lot. Mar 19 at 14:39

2 Answers 2

1
=LET(salaries,B4:AA4, raises,B7:AA207 
   BYCOL({salaries; raises}, LAMBDA(c, 
     IF(INDEX(c,1)>0, SUM(c),))))
  • Range B7:AA207 provides for 200 rows of raises data.
  • Range B4:AA4 allows for up to 26 employees.
  • You can tweak the range sizes if you think you'll have more (or less) data in the future.
0

Since I am not very familiar with the Google-Sheets hence the following formula may be not that very elegant like those who are. However, this is what I have tried and came up with:

enter image description here


• Formula used in cell B5

=LET(x, ARRAYFORMULA(B4:4+BYCOL(B7:Z,LAMBDA(x,SUM(x)))), CHOOSECOLS(x,SEQUENCE(1,ARRAYFORMULA(MATCH(7^89,1/(2:2<>""),1))-1)))

May be there is more elegant way of resolving this. Using Excel I would have done the following as well:

enter image description here


• Formula used in cell B5

=LET(
     _LastColumn, COLUMNS(TOROW(2:2,1))-1,
     _LastRow, MATCH(2,1/(A:A<>""))-6,
     _Names, B2:INDEX(2:2,,_LastColumn),
     _Increment, B7:INDEX(7:1000,_LastRow,_LastColumn+1),
     _Salary, B4:INDEX(4:4,,_LastColumn+1),
     _Salary+BYCOL(_Increment,LAMBDA(x, SUM(x))))

Note: There are chances the formula may get slower with increase in data, I have not tested with the same, assuming the reason because i am using lot of empty cells to iterate. Not so elegant solution.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.