Sumif Array with horizontal and vertical criteria

[rpm567]

Account Code	Jan	Feb	Mar	%Fixed	%Variable
6X	$100,000	$50,000	$25,000	100%	0%
5X	$200,000	$250,000	$50,000	25%	75%
4X	$150,000	$100,000	$0	50%	50%

<tbody>
</tbody>

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:

Cost Center	Fixed/Variable	Jan	Feb	Mar
100	Fixed	?	?	?
100	Variable	?	?	?

<tbody>
</tbody>

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:

Jan	Feb	Mar	State	%Fixed	%Variable
100	500	200	NY	50%	50%
1000	2000	500	CA	100%	0%
2000	600	400	CA	0%	100%
800	1200	500	NY	75%	25%

<tbody>
</tbody>

Sheet2:

State	Fixed/Variable Costs	Jan	Feb	Mar
NY	Fixed	650	1150	475
NY	Variable	250	550	225
CA	Fixed	1000	2000	500
CA	Variable	2200	900	525

<tbody>
</tbody>

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.