# How to sum across columns automatically?

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

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.

• Using `Google Sheets` one could use `=ARRAYFORMULA(BYCOL(B7:E13,LAMBDA(x,SUM(x)))+B4:E4)` and with `Excel` --> just remove the `ARRAYFORMULA()` Commented 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
Commented 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. Commented Mar 8 at 9:36

``````=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.

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:

• 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:

• 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.