# Dynamically sum each row and column by arrayformula

This shouldn't be too hard, but I can't figure it out. Please have a look at this sheet. Sample:

``````| Jan '17   | Feb '17      | Mar '17      | Apr '17   |           |
|-----------|--------------|--------------|-----------|-----------|
|           | SUM COLUMN B | SUM COLUMN C | [...]     |           |
| SUM ROW 3 | 0.00         | 0.00         | 13,954.00 | 13,954.00 |
| SUM ROW 4 | 0.00         | 0.00         | 0.00      | 0.00      |
| [...]     | 0.00         | 0.00         | 0.00      | 0.00      |
``````

I need to sum all each column in Row 2 and each row in Column A. However, since the columns and rows in this document are added dynamically, I want to accomplish this by using an arrayformula or similar.

I've tried using

``````=MMULT(B3:AK,TRANSPOSE(ARRAYFORMULA(COLUMN(B3:AK)^0)))
``````

for summing rows in column A, this will yield incorrect results when a new column is added. Extending the range to `B3:AAA` causes errors in MMULT.

Any ideas?

I would use custom functions for this.

Column sum in B2 would be `=columnSum(B3:1000000)` and row sum in A3 would be `=rowSum(B3:1000000)`. The functions perform the summation required, treating blank cells as zeros (something that `mmult` has a problem with).

``````function columnSum(values) {
return [values.reduce(function(sums, row) {
return sums.map(function(s, i) {
return (s || 0) + (row[i] || 0);
});
})];
}

function rowSum(values) {
return values.map(function(row) {
return row.reduce(function(s, x) {
return (s || 0) + (x || 0);
});
});
}
``````