It may be that what I want to do is impossible, but I certainly hope that someone has a solution because I can't figure anything out:

I am dealing with a trial balance for a company where they have used Subtotal to sum the entire sheet as well as add some subtotals throughout the data (see attached spreadsheet for example, Columns G and H, I have highlighted in red the rows that change). On some of the rows instead of using Subtotal I'd really like to be able to use a SumIf. From time to time the people using this spreadsheet want to be able to add an account, and if they do I don't want them to accidentally forget to update one of the subtotals to include the new account. Unfortunately, if I use SumIf's in the spreadsheet the subtotals don't ignore those rows and it throws my whole spreadsheet out of balance.

I tried doing =SUBTOTAL(9, SUMIF(criteria)), but as you know this doesn't work.

Is there a way to accomplish what I'm trying to do? I've searched long and hard on the Interwebs and haven't been able to come up with anything (most posts relating to Subtotal are for people trying to ignore filtered rows and involve some sort of SUMPRODUCT formula, which wouldn't solve my problem).

Thanks in advance for your wisdom.

Dummy Data Excel Help.xlsx