summing from a blank cell upwards to the next blank cell

Hello,

I'm trying to create a formula that sums from a specified cell, up the column, until it reaches a blank cell. For example, if I had the following starting in A1

A1 | 1
A2 | 2
A3 | 3
A4 | {blank}
A5 | 4
A6 | 5
A7 | {blank}
A8 | 6
A9 | 7
A10 | 8
A11 | 9
A12 | {blank}

I would like to have a formula in the B column that can propagate down. When it reaches B4 I would like it to sum A1:A3, when it reaches B7 it should sum A5:A6, when it reaches B12 it should sum A8:11.

Any ideas?

Try this in B2:

=IF(A2="",SUM($A$1:A2)-SUM($B$1:B1),"")

Copy down.

Thanks Phuocam!

That works for what I asked for, but now I realize that I won't be able to adapt it as I would like.

I want the sheet to list prices in USD in Column A, and in CAD in Column B. In column C I will choose CAD or USD, Column D will then sum the appropriate column (A or B) up to the previous blank cell. I've attached a cells sheet to kinda show what I mean.

In D2:

=IF(C2="","",SUM(OFFSET($A$1,LOOKUP("zzzzz",$C$1:C1,ROW($C$1:C1)),0+(C2="CAD"),
LOOKUP("zzzzz",$C$1:C2,ROW($C$1:C2))-LOOKUP("zzzzz",$C$1:C1,ROW($C$1:C1)),1)))

That worked really well! Thanks.

If you could just help me understand one thing. What do the "zzzzz" parts of the formula mean?

Actually, one more point. Playing with the formula I'm noticing a few more ways that I need to adapt it.

In column C I have more options that just "USD" and "CAD". I can also choose "Visa" or "MasterCard" which adds 5% to the USD number, and "Paid" or "Invoice" which leaves it blank. Is it possible to incorporate these into the formula?

That worked!

Thank you so much!