# Calculated range based upon some criteria

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?

-

## 3 Answers

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.

-
Nice formulas ! – Senseful Aug 12 '10 at 7:43

With the following piece of code, you can do just that.

## Code

``````function mySum(range) {
var sum = 0, counter = 0, c = 0, output = [], index;
for(var j = 0, jLen = range.length; j < jLen; j++) {
if(range[j].indexOf('') !== 0) {
sum += range[j][0];
counter++;
} else {
index = c * (j - counter);
output[index]= sum;
sum = 0;
c = 1;
counter = 0;
}
}
return output;
}
``````

## Explained

First a number of variables are declared and set (`= 0` or `[]`). In the `for` loop, a check is made whether, during the iteration, the value is an empty string (`''`) or not. Key is to reset the counter and the sum, after hitting an empty cell.

## Note

Wasn't able to implement your solution. The script also makes it possible to easily implement other tasks, other than a summation.

## Example

I've created an example file for you: Calculated range based upon some criteria.
Add the script under Tools>Script Editor and press the save button (no authentication needed).

-

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.

-