1

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?

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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