Sum of LAST Column in Google Sheets

Question

I'm wondering if there's a way to sum the last Column in a sheet. I add on data every month and have to re-do multiple formulas every time I do, as they're always based on the sum of the last column.

For example, in this photo, I'd get a value that would only be the sum of D:D, but if I added a column of data in E, I would get the sum of E:E.

Answer 1

There are many ways to get the last column. i.e. you could use an array formula to return the column index if the the cells aren't empty then get the maximum. Once you have the column index you could get the column by using OFFSET among other functions.

This formula returns the column index of the last column having values on row 1.

=ARRAYFORMULA(MAX(IF(ISBLANK(1:1),,COLUMN(1:1))))

Let say that the above formula is in A2, then the following formula returns the values of the first ten cells of the last column having values.

=OFFSET(A1,0,A2-1,10)

Let say that the above formula in in A3, then just apply

=SUM(A3:A13)

You could put all in a single formula, but first, be sure that each part work properly as it will be easier to fix each part if they are separate.

Answer 2

• this will give you a sum of last column (in this case D2:D)

  =ARRAYFORMULA(SUM(INDIRECT("Sheet1!"&
   ADDRESS(2,                MAX(IF(Sheet1!1:1<>"", COLUMN(Sheet1!1:1), )), 4)&":"&
   ADDRESS(ROWS(Sheet1!A:A), MAX(IF(Sheet1!1:1<>"", COLUMN(Sheet1!1:1), )), 4))))/2

  

• note the /2 correction because row 9 already has a sum