I needed to add a summing column to a Google Spreadsheet. The spreadsheet contained monthly traffic breakdowns exported automagically from Google Analytics using the awesome Supermetrics and looked something like the sheet below. I wanted to add in the column at the end showing the year total for each page (highlighted yellow).
Pretty easy right?
Just a case of putting =sum(B:N) in the last column of the first row then filling it down for all the rows. But there are two problems with this. Firstly filling down is pretty easy when you have 10, 20 or 100 rows. When you have several thousand it's a bit more of a pain, though there are shortcuts to make it easier.
The second more fundamental problem is that when you add extra rows to the sheet, the new rows won't contain the formula. In my case this meant that every time I refreshed the GA data, if new pages had been added, these wouldn't have the formula in the totals column. Since in other sheets, I use this column to calculate yearly subtotals this meant that the numbers in all my dependent sheets would be wrong. Therefore it was really important that this value was present and correct.
So here's what I came up with to automatically populate the entire column with row level subtotals.
=arrayformula(mmult(if(isblank(B2:M), 0, B2:M),transpose(sign(column(B:M)))))To adapt it for your own sheet, the first references should include the row index to start from, the last one should just include the columns.
Hope this helps someone else with the same problem.