Dynamic SUMIF in VBA

Greetings from new member!
I have some experience in VBA and Excel in general, but this problem has me stumped. I am working with data from Access and creating a heirarchical database, with parent-child relationships. The data is grouped into five main categories. Each of them is further divided into smaller groups and so on until the lowest level of data. The data need to be summed up to the next-higher level. It's easy for the first heirarchy - I wrote the code to put SUM functions to capture the hardcoded raw data below. Now I need to write code that will selectively sum.
In the attachment, I need to develop code that will add Sub-Major Parts below Major Part 1, but stop at Major Part 2. Then repeat for those below Major Part 2, stopping at Major Part 3. And so on.
I hope that makes sense to someone. Thanks!

True, but I was concerned about proprietary data. I was wondering if someone could just give me a hint as to how to write SUMIF functions depending on an index value. I'll keep working it. Thanks for your time.