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.

  • 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

In Excel You can also use this version


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


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:



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:


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:


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:


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.

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.