Google-sheets – How to autofill a formula referring to a column into a row on Google Sheets

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

You can accomplish this in two steps:

1. Create a new sheet Sheet2 and use the following formula for A1 (or wherever you want the column to begin): =unique(Sheet1!A:A). This will give you a vertical column of simply A, B, C, and D as the unique values in Sheet1's A column.

2. Combine transpose and filter in the cell adjacent to each cell in Sheet2 as follows: =transpose(filter(Sheet1!B:B,Sheet1!A:A=A1)). Here, you are filtering the second column of your Sheet1 according to whether or not the letter next to in Sheet1 matches the latter next to it in Sheet2. This normally prints vertically, so you use transpose to make it print horizontally, i.e. in the same row as you show above. Then, drag the formula down to apply it to each successive row.

The formulas in Sheet2 end up looking like this: