# Sum rows of a array function that outputs a matrix [Google Spreadsheet]

I got a arrayfunction that is returning a matrix. I want to edit this to only return a vector, where this vector contains the sum of each row. I tried applying sum(), but that summed every element of the matrix. It is important to keep it just to one formula, not one array-formula for each row.

I am working in Google Spreadsheet, but If you only know a solution for Excel, please post it, and I might be able to convert it.

-

## migrated from superuser.comMar 24 '15 at 20:11

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

Can you be more specific? What is the formula? – benshepherd Mar 24 '15 at 8:18
Not sure about Google Sheets, but in Excel MMULT would be what you require. – XOR LX Mar 24 '15 at 8:31
Duplicate from Stack Overflow. I added my answer because I missed the "one formula" requirement and then added the answer from Stack Overflow for the sake of completeness. – Engineer Toast Mar 24 '15 at 12:49

## 2 Answers

In Excel You can also use this version

`=SUMIF(OFFSET(A1:C15,ROW(A1:C15)-ROW(A1),0,1),"<9.99E+307")`

confirmed with CTRL+SHIFT+ENTER

assuming your matrix is in `A1:C15`

That has a small advantage over `MMULT` - it still works if some of the cells in `A1:C15` are blank or don't contain numbers

However you can amend the `MMULT` version to work with a partially populated range too, i.e. with this version

`=MMULT(IFERROR(A1:C15+0,0),TRANSPOSE(COLUMN(A1:C15)^0))`

again confirmed with CTRL+SHIFT+ENTER

In Google sheets I couldn't make the SUMIF version work - but the MMULT option works with the addition of "arrayformula" function, i.e. this version:

`=arrayformula(mmult(iferror(A1:C15+0,0),transpose(column(A1:C15)^0)))`

-
+1 for making it work in Google sheets and with blanks. – Engineer Toast Mar 25 '15 at 12:04

## An Excel Solution with One Formula for Each Sum Value

It sounds like you want the `OFFSET` function. I'm going to use `MATRIX()` as a stand-in for whatever function you have that is returning a matrix. If you want to show sums for the first 3 rows of that matrix, the formulas would be as follow:

``````=SUM(OFFSET(MATRIX(),0,0,1)
=SUM(OFFSET(MATRIX(),1,0,1)
=SUM(OFFSET(MATRIX(),2,0,1)
``````

The format is `OFFSET(Reference,Rows,Columns,[Height],[Width])`. There's a more detailed explanation of the arguments on the MS Support page but the key points is that `Rows,Columns` describes how far to offset from the top-left cell in the `Reference` range and `Height,Width` describes how large to make the array from that offset cell. You want it to be 1 row high and the same width as the original so use 1 for `Height` and just leave `Width` blank as it will default to be the same width as `Reference`.

## An Excel Solution with One Formula Returning a Vector of Sums

This question is a duplicate of one over at Stack Overflow. If you want to return the array, the formula is:

``````MMULT(MATRIX(),TRANSPOSE(COLUMN(MATRIX())^0))
``````

Note the lack of an equal sign because this formula by itself in a cell will return the `#VALUE!` error. Hopefully, this is something you can work with in your application. I tested it to make sure it worked by summing those values with the array formula:

``````{=SUM(MMULT(MATRIX,TRANSPOSE(COLUMN(MATRIX)^0)))}
``````

Of course, this is just a needlessly complicated version of `=SUM(MATRIX())` but it worked as a test. In Excel, it also allows you to step through the calculation with the `Evaluate Formula` function on the `Formulas` ribbon and it'll show the array result given by `MMULT`.

-