Google sheets ArrayFormula SUM Ouput in all colomns

1
votes

Let's have the following matrix:

 A B C ...
1
2
3
4

A1 - C3 are filled with numbers. A4 - C4 should output the sum from the cells aboth.

Arrayformula(SUM(A1:C3)) only deliveres one output with the sum of all cells. I already found some formulas for sum of arrayformulas but the output was always underneath the array instead of fulfilling to the right...

So it should be like:

   A   B   C ...
1 "1" "3" "2" 
2 "1" "2" "3"
3 "3" "1" "2"
4 "5" "6" "7"

But the formula should not be limited to A-C it should go from A - unlimited

Thank you.

1
google-sheetsarray-formulas

1 Answers

0
votes

A well known solution to this problem is matrix multiplication. Put the following formula in A4:

=MMULT({1;1;1}, A1:C3)

or if your spreadsheet is set up to use the semicolon as a main separator instead of the comma:

=MMULT({1\1\1}; A1:C3)

Explanation: it's really matrix algebra, by multiplying a horizontal vector of 1's by the matrix, one obtains a horizontal vector of the vertical sums of the matrix columns. Explanations of matrix multiplication as such can be found on the Wiki and in many other places. There are also many examples to be found of using MMULT in spreadsheets by googling "matrix multiplication" +spreadsheets or +excel etc.

A particularly great set of examples on using MMULT is here: https://docs.google.com/spreadsheets/d/1NJPAt5iFQWIyxHx35bpCh6zjVeKon4rdTPk-dfyZDrU/edit#gid=748926120