11

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.com Dec 20 '13 at 4:18

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

7

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

  • 2
    @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
  • 2
    @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
2

How about manually summing the columns:

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

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.