Last Updated: June 21, 2018
· adamphillips

Magic formula for automatically summing rows in a Google Spreadsheet

Adding an automatic 'totals' column to a Google Spreadsheet is trickier that it looks.

You can use sum and fill down but each time you add a new row it won't automatically add the formula to the new row.

So came up with this. This is for a spreadsheet where the 1st row and column are header information and the columns B to M should be included in the sum.

=arrayformula(mmult(if(isblank(B2:M), 0, B2:M),transpose(sign(column(B:M)))))

For a more thorough example check out http://blog.29ways.co.uk/adding-a-summing-column-to-a-google-spreadsheet/

2 Responses
Add your response


Thank you!

over 1 year ago ·

I don't know why, but I'm getting an error that says: "Function MMULT parameter 1 expects number values. But ' ' is a text and cannot be coerced to a number". I know your code recognizes blank cells, so why would I get this error? If anyone can help me troubleshoot, it would be greatly appreciated!

over 1 year ago ·