18

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.

Improve this question
1
  • There's a great answer on StackOverflow using the new LAMBDA function. =BYROW(A:N; LAMBDA(x; SUM(x))). I'm not submitting it as an answer as it's not my work. stackoverflow.com/questions/21797621/… Commented Mar 2, 2023 at 9:45

4 Answers 4

Reset to default
14

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

Improve this answer
5
  • 2
    @YisraelDov, remember to mark the answer as accepted if it was what you were looking for. Commented Dec 2, 2014 at 9:04
  • This is a remarkable answer. Commented Oct 30, 2016 at 22:10
  • Any idea on how to make it work with blank cells? (to be treated as 0) Commented Oct 30, 2016 at 22:26
  • 3
    @MichaelLitvin: try this: =ARRAYFORMULA(SUMIF(IF(COLUMN(B1:E1),ROW(B2:E4)),ROW(B2:E4),B2:E4)). ht to @AdamL. Commented Oct 31, 2016 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)))) Commented Apr 22, 2018 at 22:42
3

How about manually summing the columns:

=arrayformula(A:A + B:B + C:C)
Improve this answer
2
  • Can’t be sure the poster meant N as a placeholder for a unknown number of columns or if the poster really meant column N. If the number of columns cannot be determined ahead of time, then explicitly naming each and every column in the formulae is not an option. Commented Jan 1, 2020 at 22:07
  • This can be cumbersome if you have lots of columns Commented Dec 12, 2020 at 20:15
0

Use a modified version of the answer by Jacob to include blank cells.

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

Alternatively, you can use the following simpler form as well (excludes column function, please note the difference in the second cell reference B2:E2 instead of B2:E4):

=MMULT(arrayformula(B2:E4 * 1),TRANSPOSE(ARRAYFORMULA(B2:E2^0)))
Improve this answer
0

Taking collection of data to be summed as B2:E4;

Simplified version of SUM OVER ROWS, coercing blanks to zeroes:

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

Corollary: SUM OVER COLUMNS, coercing blanks to zeroes:

=ARRAYFORMULA(MMULT(TRANSPOSE(ROW(B2:E4))^0,B2:E4*1))
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.