Calculated range based upon some criteria

Question

Given this layout:

+--------+      +--------+
| A  | B |      | A  | B |
+----+---+      +----+---+
| 1  |   |      | 1  | 6 |
| 2  |   |      | 2  |   |
| 3  |   |  =>  | 3  |   |
|    |   |      |    |   |
| 4  |   |      | 4  | 9 |
| 5  |   |      | 5  |   |
|    |   |      |    |   |
+----+---+      +----+---+

I want to have the sum of A1:A3 in B1 and the sum of A5:A6 in B5. This is trivial. what i really want is a formula which calculates the range A1:next_free_cell_below_in_a and A5:next_free_cell_below_in_a.

Bonus: Can I use some other "patterns" / "criterias" as well?

How to achieve this?

Answer 1

For this solution, one dummy row (1:1) & one dummy column (C:C) is used.

Formula:
B2 contains =IF(ISBLANK(A1),C2,""), then copied down to B3:B7
C2 contains =A2+IF(ISBLANK(A3), 0, C3), then copied down to C3:C7

   |    A    |    B    |    C
---+---------+---------+---------
 1 |         |         |         
 2 |       1 |       6 |       6  (ie. 3+2+1)
 3 |       2 |         |       5  (ie. 3+2)
 4 |       3 |         |       3  (ie. 3)
 5 |         |         |       9 
 6 |       4 |       9 |       9  (ie. 5+4)
 7 |       5 |         |       5  (ie. 5)

A:A : Data
B:B : Desire output
C:C : Dummy column

I am not so sure about what you mean by other "patterns" / "criterias". Maybe you can describe more about that.

Answer 2

it took some time but here it is:

 =SUM(OFFSET(A1,0,0,INDEX(ROW(A1:A),MATCH(TRUE,ISBLANK(A1:A),0))-ROW(A1),1))

how it works:

1. we want the sum of a range. OFFSET provides a range, based upon a starting point (optional offset in x and y) and then some dimensions.

2. A1 is the reference corner, 0 and 0 are the offsets (we want to stay in the A column). the 1 at the end means 'only the A column'.

3. the 'height' parameter of the OFFSET function is calculated by row of empy cell - ROW(A1) (which is the starting cell)

4. INDEX(ROW(A1:A), MATCH(TRUE, ISBLANK(A1:A), 0)) calculates the rownumber of the first empty cell below A1.

if someone could explain the reason why 4. works as it does, please comment my answer.