Sum rows until it reaches a certain row

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.

Datasets

Accepted answers

gabrielvilella

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.

Departments.yxmd

All comments

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

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.

Departments.yxmd

D_Y

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703