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.