In Google Sheets, I would like to get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column ONLY if the total is greater than zero, (I don't want to see 0's when the column is blank). Can I do that with a SUMIF function, (if yes, how?) or, I need to use an IF function?

1 Answer 1


paste into any cell of A column:

=IF(SUM(INDIRECT("A2:A"&ROW()-1))>0, SUM(INDIRECT("A2:A"&ROW()-1)), )

  • The problem with that is that it's not dynamic, if I drag this formula to the right of this cell, it doesn't change, i.e. A2:A stays the same in all columns, as opposed to becoming B2:B, C2:C, etc. How do I make it "column dynamic"?
    – Bilha
    Jul 25, 2019 at 8:09
  • in that case use: =IF(SUM(INDIRECT(ADDRESS(2, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN(), 4)))>0, SUM(INDIRECT(ADDRESS(2, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN(), 4))), )
    – user0
    Jul 25, 2019 at 9:38
  • You are awesome!
    – Bilha
    Jul 26, 2019 at 14:56
  • Now, how do I get at the bottom-most cell of a column the sum of each cell of that column, except if the A cell of that row = "Fish" (and except for row 1, the header)? This needs to be relative, as I would like to drag the formula to the right for all columns.
    – Bilha
    Jul 26, 2019 at 15:05
  • can you share a copy of your sheet?
    – user0
    Jul 26, 2019 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.