Google Sheets – How to Sum a Column at the Bottom

formulasgoogle sheetsgoogle-sheets-arrayformula

In Google Sheets, how do I get – at the bottom-most cell of a column – the sum of each cell (except for row 1, the header) above in that column? Keep in mind, I may add rows in the future.

Best Answer

There are various ways how to solve this. The most simple would be to use dragging SUM:

Then all you need to do is insert new rows inside of the SUM range so it would automatically expand as needed:

As a next-level move, you can use ARRAYFORMULA with INDIRECT where you can use ROW()-1 to always get the range from A2 cell to the row where this formula resides minus 1 row:

=ARRAYFORMULA(TRANSPOSE(MMULT(
 TRANSPOSE(INDIRECT("A2:C"&ROW()-1)),
       ROW(INDIRECT("A2:A"&ROW()-1))^0)))

Formula

=SUM(B2:$B$7)

=ARRAYFORMULA(SUMIF(ROW(A2:A7);">="&ROW(A2:A7);B2:B7))

Explained

If you add the first formula in C2, then you're able to copy that cell (drag) to C7. The ARRAYFORMULA will present the result at once.

Example

I've created an example file for you: Inverse Cummulative Sum

Reference

Cummulative Sum

Google-sheets – Sum column by referencing column header

You can use the following formula to accomplish that.

Formula

=SUM(INDIRECT(B1 & ":" & B1))

Explained

This formula can be used for rows and columns a like. It simply combines the input into a text range, that's being converted into a readable range, through the INDIRECT function.

Screenshot

enter image description here

Example

I've prepared an example file for you: sum column by referencing column header

Best Answer

Related Solutions

Google Sheets – SUM of All Cells in Another Column Except Above Rows

Formula

Explained

Example

Reference

Google-sheets – Sum column by referencing column header

Formula

Explained

Screenshot

Example

Related Topic