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

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 ·