# Excel formula for collapsing adjacent rows with a sum

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

`=IF(B1=1,SUM(C1:INDEX(C1:C\$1000,IFERROR(MATCH(TRUE,INDEX(B2:B\$1000=1,,),0),MATCH(10^10,C:C)))),0)`
• 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