2

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.

  • Thanks "user0". What would it be, for that same case, if I want to sum up the column ONLY if the sum is greater than zero? (I just don't want to see 0's when the column is blank. Also, I would like this to apply on all columns from C onwards, and I want to be able to add rows anywhere, (even before the last row - so, plain SUM function wouldn't fit for me.)) – Bilha Jul 25 '19 at 6:45
1

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

0

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

0

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)))

0

| improve this answer | |
0

The formula provided is correct but does not take under consideration empty cells.

To fix the empty cells issue use the following corrected formula.

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

enter image description here

(notice the *1)

| improve this answer | |
  • Please check this answer which fixes the empty rows issue. – marikamitsos Dec 26 '19 at 9:48

Your Answer

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.