Google-sheets – How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns

google sheets

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.}

Best Answer

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

Related Solutions

Google Sheets Queries – Removing Header and Blank Row from SUM, GROUP BY Query Results

To suppress the header, end the query string with label sum(A) ''.

The blank spot below the header comes up because the column B, besides the letters a,b,c, also contains a bunch of blank cells, which form a group of their own. Since you don't want them, add the clause where B<>''.

End result:

=query(A1:B, "select sum(A) where B<>'' group by B label sum(A) ''")

(Case doesn't matter: I prefer lowercase because more text fits into the same horizontal space.)

Google Sheets Formulas – How to Sum a Column if Another Column Contains Any of the Values

Short answer

=ArrayFormula(sum(sumif(A:A,{"Bus";"Train"},B:B)))

Explanation

A:A is the column containing the categories.
{"Bus";"Train"} is a 1x2 array. It could be replaced by a range 1xn arrange containing the categories to be included in the sum.
B:B is the column containing the values to be summed.
sumif returns of the sum of values corresponding to each specified category
sum returns the sum of the returned array by sumif
ArrayFormula :

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.