Sum rows until it reaches a certain row

[D_Y]

I have a worksheet that looks like this:

Departments	Value
Blue Department Total	0
Blue Department 1	2
Blue Department 2	1
Red Department Total	4
Red Department 1	4

I'm trying to fill in the value for Blue Department Total to be 3, similar to how Red Department Total is already filled out correctly. I need this to be dynamic as some departments are filled out correctly and some aren't. 

I tried using a Multi-Row formula but wasn't sure how to sum a dynamic number of rows before stopping at the next "section", e.g. Red Department in this case. 

[mattvandivier]

Instead of trying to configure a dynamic multi-row formula, have you considered using the cross-tab tool to separate your data out into a column for each department, sum it from there, and then reshape it all back using the transpose tool into the format you ultimately need?

[gabrielvilella · Accepted solution]

Hi @D_Y, please see one way of solving this. Of the final solution may depend on what you actually have on you data for the department names.

[D_Y]

Thanks!