0

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.

enter image description here

New contributor
Stephen Perlstein is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

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.

0
  • 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

    0

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

Your Answer

Stephen Perlstein is a new contributor. Be nice, and check out our Code of Conduct.

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.