0

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the column above. Consider the following example:

       A | B | C
  ---------------------------------
         |   | Formula on blank row
  Apple  | 1 |
  Apple2 | 2 |
         |   |
  Bravo  | 3 |
  Bravo2 | 5 |

The sum of A should return (1 + 3) which is 4.

I tried to use the following function in C1:

=SUMIF(B2:B,"<>"&"",A1:A)

This returns 0.

2 Answers 2

2

Turns out I was using the SUMIF formula wrong. This works:

=SUMIF(A1:A,"",B2:B)
1

If you are ok with using closed ranges, try:

=sum(ArrayFormula(if(B2:B100="", offset(B2:B100,1,0),0)))
2
  • Interesting function, offset. Unfortunately, this formula equals 3, and not 4. I do think this is the right strategy, though. I will work with this equation a bit.
    – paragbaxi
    Feb 15, 2015 at 4:42
  • 1
    If you have row 1 blank, start your range from there.. =sum(ArrayFormula(if(B1:B100="", offset(B1:B100,1,0),0)))
    – JPV
    Feb 15, 2015 at 7:08

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.