2

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.

  • It seems I might use MATCH() and search for an empty row below the current. For some reason MATCH("", B3:B, 0) does not seem to match empty rows, even though it seems it should.. – Gurgeh Sep 18 '14 at 14:46
1

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

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.