Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

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.

share|improve this answer
    
Nice formulas ! –  Senseful Aug 12 '10 at 7:43
add comment

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.

Screenshot

enter image description here

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).

share|improve this answer
add comment
up vote -1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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