Subtotals within Sumifs range

[jaz44]

I have a report summarizing different expense types that is grouping individual expense accounts on a downloaded report on a different sheet into general types using a sumifs function.

Is there a way to add a subtotals of those individual expense types as a 2nd sub-category without upsetting the range of larger sumifs function? I'm trying to avoid adding multiple sumifs so that when I update the reference column being summed on a monthly basis I only have to update it in the first cell of the sumifs range like I do today.

If that's not clear, below is what I have now and where I'm trying to go.
"Column B" would be where the sumifs are, and ideally I'd be able to insert the subtotals for expense categories 2, 3, 4, etc. without upsetting the ranges in the expense type formula or adding another helper column / formula in my data sheet since right now the categories only "exist" where the subtotal would be inserted.

Thanks!

Current

Expense Category 1
Expense Type A	$100
Expense Type B	$200
Expense Type C	$300
Expense Category 2
Expense Type D	$400
Expense Type E	$500
Expense Type F	$600
Total Expenses	$2,100

Future

Expense Category 1	$600
Expense Type A	$100
Expense Type B	$200
Expense Type C	$300
Expense Category 2	$1,500
Expense Type D	$400
Expense Type E	$500
Expense Type F	$600
Total Expenses	$2,100

 

[Cubist]

Have you considered using Pivot Table?

 

[jaz44]

Have you considered using Pivot Table?

If it was just for me that would work. The end result goes out to a larger group that won't manipulate one to get to the right data, I've got 5 sheets with the same formula issue, and 2 spots within each sheet where the "fix" would go. Plus each one has it's own formatting, etc. that doesn't work with a pivot table, and I want to keep the monthly update process as close as possible to updating the download and moving the sum columns to the right one letter so that I can hand it off.

My thought was sumproduct or index match, but I don't use either one often enough to know for sure if they'll work or not.

 

[Cubist]

What does the raw data look like?

 

[jaz44]

It's a little convoluted, but it works for this report. We also use it for a second similar, but different, report that is based on the same data, but needs a completely different assignment of expense types.

The original raw data is cost center and accounts in the rows with monthly totals for budget, forecast and actual in the columns. It also has lookups that assign my expense types based on the combinations of the cost center and account.

Then on a 2nd sheet I use the first expense type assignment as a key to reduce the cost center / account data into the the expense types and then split those into my 5 reporting groups based on another percentages of responsibility for each of the original cost centers that the groups have, and now this on this 3rd final report sheet I'm reporting the totals and variances of each expense type for each of my 5 reporting groups.

Within each of those 5 reporting groups though, I now need to subtotal the types into this new "category".

 

[Cubist]

What I'm getting at is if your data is structured like this, you can just do SUMIFS on the Category.

Book3
	A	B	C
1	Category	Type	Value
2	Expense Category 1	Expense Type A	1
3	Expense Category 1	Expense Type B	2
4	Expense Category 1	Expense Type C	3
5	Expense Category 2	Expense Type A	4
6	Expense Category 2	Expense Type B	5
7	Expense Category 2	Expense Type C	6
8	Expense Category 3	Expense Type A	7
9	Expense Category 3	Expense Type B	8
10	Expense Category 3	Expense Type C	9
Sheet5

 

[jaz44]

No, the category only exists in the 3rd / final reporting sheet after all the other grouping and splitting happened.

I added another helper column in my middle sheet that introduces the category and puts it into a new column based on the expense type. Then I added an If on my sumifs so that if the first result is 0 it uses the new column instead, and it worked.

My issue now is that I'll have to explain how to update the column reference in only 2 of the 3 sumifs. Is there a way to enter the columns and / or rows I want in the range in a cell so that all of the formulas update correctly with one cell update?

Right now one of my formulas would be =IF(SUMIFS('Sheet1'!E$636:E$945,'Sheet1'!$D$636:$D$945,$B4:$B45)=0,SUMIFS('Sheet1'!E$636:E$945,'Sheet1'!$AW$636:$AW$945,$B4:$B45),SUMIFS('Split Cost Center Grouping'!E$636:E$945,'Sheet1'!$D$636:$D$945,$B4:$B45))

Can I use indirect to make my combos of E, D, AW, 636 and 945 be "pulled" from a cell reference vs. hard coded in the formula?