0

I have a spreadsheet with values like so:

blah   1    12
       2    12
       3    12
foo    1     6
bar    1     2
foo    1     6
       2     5

Numbers in the middle column range from 1 to 8; numbers in the right column range from 1 to 12. The middle column is always in order with no gaps, but can reset back to 1 after any value. The left column always has a nonblank (but not necessarily unique) value on a 1 row and a blank value on a >1 row. The values for the >1 rows are usually the same as the 1 row, but not always (and I don't want to assume they are).

I would like to add a new column with a single formula that, for each row:

  • if the first number is a 1, then sum all of the second numbers up to but not including the next row with a 1.
  • if the first number is anything else, then output 0.

ie. the required output for the data above is:

36
 0
 0
 6
 2
11
 0

If it's easier, the sum could appear on the highest-numbered row of each subgroup instead of the 1 row (though I definitely prefer it on the 1 row). But the row relationships have to be maintained -- it can't output only 4 rows.

Alternatively, the grouping decision could be based on the blank/nonblank column instead of the counting column; again, if that's easier.

  • Does it have to be a cell formula or could it be VB script? – Jonny Wright Jun 6 '14 at 8:15
  • A formula is preferred but VB script would be ok too. I'm hoping for something not too large because the spreadsheet isn't saved as one; it's regenerated from CSV data periodically, so I have to re-enter formulae to calculate things. – Miral Jun 6 '14 at 8:34
1

Assuming the table you as you give it is in A1:C7, in D1 and copy down:

=IF(B1=1,SUM(C1:INDEX(C1:C$1000,IFERROR(MATCH(TRUE,INDEX(B2:B$1000=1,,),0),MATCH(10^10,C:C)))),0)

Adjust the 1000 to a sufficiently higher row number if required.

Note that this solution assumes the values in column C are numeric, and not e.g. numbers stored as text.

  • I'm still not entirely sure how that works, but it works. One change I did make though was to use a relative reference (C1:C12 instead of C1:C$1000) since it's easier to define a maximum number of subrows before the 1 repeats than it is to define the maximum number of rows overall. Also this gives an error on the very last row, but that's easily worked around. – Miral Jun 9 '14 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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