Summing Multiple Intersections Based On Multiple Column/Row Titles

[mysites]

Hey Everyone,

I've been stewing on this one for so long my head is steaming. I need a hand.

I have a workbook with five worksheets. Tab One is my output tab and has a number of cells that require a formula. Tab Two is a mapping table that looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]Type
[/TD]
[TD]Group Mapping
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Ayy[/TD]
[TD]Variance[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Bee[/TD]
[TD]Sum[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Cee[/TD]
[TD]Sum[/TD]
[TD]Group 3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Dee[/TD]
[TD]Sum[/TD]
[TD]Group 3[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Eee[/TD]
[TD]Variance[/TD]
[TD]Group 3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Eff[/TD]
[TD]Sum[/TD]
[TD]Group 4[/TD]
[/TR]
</tbody>[/TABLE]

Tab Three is another mapping file:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD]City Mapping
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]One[/TD]
[TD]Chicago[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Two[/TD]
[TD]Chicago[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Three[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Four[/TD]
[TD]Detroit[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Five[/TD]
[TD]Detroit[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Six[/TD]
[TD]Miami[/TD]
[/TR]
</tbody>[/TABLE]


Tab Four is where my Variance Data sits.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD]Ayy[/TD]
[TD]Bee[/TD]
[TD]Ayy[/TD]
[TD]Dee[/TD]
[TD]Eee[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]One[/TD]
[TD]1.0
[/TD]
[TD]2.0[/TD]
[TD]3.0[/TD]
[TD]4.0[/TD]
[TD]5.0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Two[/TD]
[TD]6.0[/TD]
[TD]7.0[/TD]
[TD]8.0[/TD]
[TD]9.0[/TD]
[TD]10.0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Three[/TD]
[TD]11.0[/TD]
[TD]12.0[/TD]
[TD]13.0[/TD]
[TD]14.0[/TD]
[TD]15.0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Three[/TD]
[TD]16.0[/TD]
[TD]17.0[/TD]
[TD]18.0[/TD]
[TD]19.0[/TD]
[TD]20.0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Four[/TD]
[TD]21.0[/TD]
[TD]22.0[/TD]
[TD]23.0[/TD]
[TD]24.0[/TD]
[TD]25.0[/TD]
[/TR]
</tbody>[/TABLE]

Tab Five is where my Sum Data sits.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD]Ayy[/TD]
[TD]Bee[/TD]
[TD]Ayy[/TD]
[TD]Dee[/TD]
[TD]Eee[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]One[/TD]
[TD]100.0
[/TD]
[TD]200.0[/TD]
[TD]300.0[/TD]
[TD]400.0[/TD]
[TD]500.0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Two[/TD]
[TD]600.0[/TD]
[TD]700.0[/TD]
[TD]800.0[/TD]
[TD]900.0[/TD]
[TD]1000.0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Three[/TD]
[TD]1100.0[/TD]
[TD]1200.0[/TD]
[TD]1300.0[/TD]
[TD]1400.0[/TD]
[TD]1500.0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Three[/TD]
[TD]1600.0[/TD]
[TD]1700.0[/TD]
[TD]1800.0[/TD]
[TD]1900.0[/TD]
[TD]2000.0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Four[/TD]
[TD]2100.0[/TD]
[TD]2200.0[/TD]
[TD]2300.0[/TD]
[TD]2400.0[/TD]
[TD]2500.0

[/TD]
[/TR]
</tbody>[/TABLE]

On Tab One, my output tab, I need to sum together all of the intersections at Group 1 and Chicago (As well as 50+ other cells that contain some combination of Group and City, so hard-coding the mappings in the formula would not work for me). Members from Tab 2 with the Type of Variance need to be pulled from my Variance Tab, and those with the type of Sum need to be pulled from my Sum tab.

So I would expect the calculated value of my cell to equal 918 (18 from the Variance Tab plus 900 from the Sum tab).

I've tried creating a micro version of this formula using SUMPRODUCT, but I haven't been able to find a formula for SUMPRODUCT that takes multiple values as criteria. For instance: =SUMPRODUCT((A2:A5="Bee")*(B1:E1={"Three","Four"})*(B2:E5))

I receive #N/A when entering this formula for a single-table, small scale version of the functionality. Is there a way to pass a range to SUMPRODUCT? Or is this out of the capabilities of that function?

 

[mysites]

Of course, the above formula wouldn't work on my example tables because the rows and columns are swapped. If I were to try the formula on the table above, it would look like this: =SUMPRODUCT((A2:A5={"Three","Four"})*(B1:E1="Ayy")*(B2:E5))