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.
Solved! Go to Solution.
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?
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.
Thanks!