How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?

In Google Sheets, I have the following data:

|   A |  B  |  C  |  ...  | N  | TOTALS       |
1 |  123|  425|  324|  ...  | 234| =SUM(A1:N1)  |
2 |  123|  425|  324|  ...  | 234| =SUM(A2:N2)  |
3 |  123|  425|  324|  ...  | 234| =SUM(A3:N3)  |

How can I create the TOTALS column using ARRAYFORMULA?

ARRAYFORMULA(SUM(A1:C99)) will give a single number, the sum of all the cells — that’s not what I want.

migrated from superuser.comDec 20 '13 at 4:18

This question came from our site for computer enthusiasts and power users.

Use the following formula to do that.

Formula

=MMULT(B2:E4,TRANSPOSE(ARRAYFORMULA(COLUMN(B2:E4)^0)))

Explained

The ARRAYFORMULA is meant to return the complete range, as set by the COLUMN formula. The ^0 (power of zero) will always return 1. Therefore each result is multiplied by 1 (yielding the same value) and summed.

Example

I've created an example file for you: SUM OVER ROWS

• @YisraelDov, remember to mark the answer as accepted if it was what you were looking for. – Alex Dec 2 '14 at 9:04
• This is a remarkable answer. – Michael Litvin Oct 30 '16 at 22:10
• Any idea on how to make it work with blank cells? (to be treated as 0) – Michael Litvin Oct 30 '16 at 22:26
• @MichaelLitvin: try this: =ARRAYFORMULA(SUMIF(IF(COLUMN(B1:E1),ROW(B2:E4)),ROW(B2:E4),B2:E4)). ht to @AdamL. – Jacob Jan Tuinstra Oct 31 '16 at 18:20
• To handle empty, one option is to use indirect instead of range. In my case I used this indirect("e3:y"&max(arrayformula((index(E3:Y,0,0)<>"")*row(E3:Y)))) – BrunoLM Apr 22 '18 at 22:42

How about manually summing the columns:

=arrayformula(A:A + B:B + C:C)