Building an advanced subtotal formula

I am trying to build an advanced subtotal formula which will subtotal based on criteria. The background is that I have an income statement that I am building with different "levels", meaning that level 1 provides high level information and the lowest level (named "New GL") will have the lowest level of detail. "New GL" is effectively a consolidation of line items "Old GL". Each line item on my income statement has a level allocated to it, and so I need to build the formula such that the higher level will subtotal its direct lower level, and here is the complexity, until it finds the next level of its own type.

For example, level 3 needs to subtotal all level 2 line items below it until the next level 3 line item. It is easy to do this if one manually runs a simple sumif/ subtotal formula, however I can't figure out how to create a generic formula which does not need manipulation for each level and line item it is subtotalling, and which caters for the bold text above. Note that levels 1, 2, 3, 4, and "New GL" (which can also be called level 5) need the formula.

I've attached a spreadsheet........hope my query has come across clearly.

Thanks

Put this formula in E7, and then copy to all of the other subtotal cells

=SUMIF(D8:$D$91,INDEX({2,3,4,"New GL","Old GL"},MATCH([@Level],{1,2,3,4,"New GL"},0)),E8:$E$91)
-SUMIF(D8:$D$91,INDEX({2,3,4,"New GL"},MATCH([@Level],{2,3,4,"New GL"},0)),E8:$E$91)

The sort of thing you're looking to do is very common but your raw data comes into various categories according to nothing more than the position of the data in the list (eg. auto versus manual posting).
Surely you can get that data in a different format - it would mean you don't have to use any formulae at all.
As an example, in the attachment I've reverse-engineered your data into a different layout on one sheet, and made a pivot table from it in the second sheet. The results, as far as I can see, are the same as yours.
The pivot table has loads of options on how and what is displayed, and you get the results instantly.
Can you get your raw data in something like the format of the first sheet?

Thank you very much; the formula works perfectly. If possible would you be able to post a brief walkthrough of the formulas and thought process so I can learn?

Thank you very much p45cal; a different perspective to a problem I thought should be solved using a formula....I'll remember this for the future.

p45cal said:

The sort of thing you're looking to do is very common but your raw data comes into various categories according to nothing more than the position of the data in the list (eg. auto versus manual posting).
Surely you can get that data in a different format - it would mean you don't have to use any formulae at all.
As an example, in the attachment I've reverse-engineered your data into a different layout on one sheet, and made a pivot table from it in the second sheet. The results, as far as I can see, are the same as yours.
The pivot table has loads of options on how and what is displayed, and you get the results instantly.
Can you get your raw data in something like the format of the first sheet?

Click to expand...