Google-sheets – Google Spreadsheet add rows until marker

google sheets

I have a spreadsheet of the form

ItemA
Subitem1 5
Subitem2 4
Subitem3 7
<potential marker>
ItemB
Subitem 4

I want to sum the values next to Subitem to a total next to ItemA, ItemB, etc. I may add additional subitems, so I would like the range in my formula to be dynamic. Instead of SUM(B2:B5), I would prefer to sum until ItemB regardless of how many subitems I add or remove.

If it helps, I can add some sort of end-of-item-marker or empty row or something where I have written "potential marker".

As far as I can see when I play around, Google Spreadsheet will not increase the formula range when I insert a row in the middle of a range.

Best Answer

Rather than add a 'marker' I have used the item names themselves, but in case they do not really start "Item", prefixed these with # (with one added to the bottom of the list also). Assuming ItemA is in A1 and 5 in B2, please try in C1: 

=if(B1<>"","",sum(indirect("B"&row()+1&":B"&row()+MATCH("#*",A2:A101,0))))

copied down to suit. This does require an extra column (so not immediately next to the item names).

Related Topic