Dynamically sum each row and column by arrayformula

Question

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?

Answer 1

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);
    });
  });
}