Sumif Array with horizontal and vertical criteria

[rpm567]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account Code[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]%Fixed[/TD]
[TD]%Variable[/TD]
[/TR]
[TR]
[TD]6X[/TD]
[TD]$100,000[/TD]
[TD]$50,000[/TD]
[TD]$25,000[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]5X[/TD]
[TD]$200,000[/TD]
[TD]$250,000[/TD]
[TD]$50,000[/TD]
[TD]25%[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]4X[/TD]
[TD]$150,000[/TD]
[TD]$100,000[/TD]
[TD]$0[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]

I have sheets relating to various cost centers and each line is the spend for a specific account. Let's call the above table as sheet1 and the summary table below, as sheet2. The summary table is like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cost Center[/TD]
[TD]Fixed/Variable[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Fixed[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Variable[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

I can't figure out the formula to fill in the cells. I have too many to do everything manually, any help would be greatly appreciated!

 

[StephenCrump]

You haven't told us what results you're expecting?

I'm guessing that the top left "?" should be $225,000, i.e. (100% of $100,000) plus (25% of $200,000) plus (50% of $150,000)?

But how do we know that Account codes 6X, 5X and 4X belong to cost centre 100?

 

[rpm567]

You haven't told us what results you're expecting?

I'm guessing that the top left "?" should be $225,000, i.e. (100% of $100,000) plus (25% of $200,000) plus (50% of $150,000)?

But how do we know that Account codes 6X, 5X and 4X belong to cost centre 100?

Okay, sorry for being vague... let me try to frame this more appropriately.

Sheet1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]State[/TD]
[TD]%Fixed[/TD]
[TD]%Variable[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]500[/TD]
[TD]200[/TD]
[TD]NY[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]CA[/TD]
[TD]100%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]600[/TD]
[TD]400[/TD]
[TD]CA[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]1200[/TD]
[TD]500[/TD]
[TD]NY[/TD]
[TD]75%[/TD]
[TD]25%[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Fixed/Variable Costs[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]NY [/TD]
[TD]Fixed[/TD]
[TD]650[/TD]
[TD]1150[/TD]
[TD]475[/TD]
[/TR]
[TR]
[TD]NY [/TD]
[TD]Variable[/TD]
[TD]250[/TD]
[TD]550[/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Fixed[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Variable[/TD]
[TD]2200[/TD]
[TD]900[/TD]
[TD]525[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for a formula to produce the values in red.
Thanks again for taking the time to help!

 

[Tetra201]

Here is the formula for cell C2 on Sheet2. It must be entered using Ctrl+Shift+Enter, not just Enter. Then it can be copied down and across.

=SUM(IF(Sheet1!$D$2:$D$5=$A2,INDEX(Sheet1!$A$2:$C$5,0,MATCH(C$1,Sheet1!$A$1:$C$1,0)))*IF(Sheet1!$D$2:$D$5=$A2,INDEX(Sheet1!$E$2:$F$5,0,MATCH("*"&$B2,Sheet1!$E$1:$F$1,0))))

Note: to the best of my understanding, you've miscalculated CA Variable.