Combined sumif or similar

[drluke]

I have 2 data tables with colours in row 1. The same colours can be in both data tables (not
necessarily in the same columns). In A2-A100 I have names. I need to sum all the colour red
sales made by Smith from both data tables into one consolidated table.
How can I do this without having to do for example (sumif table 1) = (sumif table 2)
Summary
	A	B	C	D	E
1		Red	Yellow	Green	Black
2	Smith
3	Jones
4	Wright
5	Bloom
Table 1
	A	B	C	D	E
1		Green	Black	Yellow	Red
2	Smith	20	56	387	100
3	Jones	36	145	459	583
4	Wright	89	698	33	758
5	Bloom	101	211	56	921
Table 2
	A	B	C	D	E
1		Black	Red	Green	Yellow
2	Smith	685	387	56	20
3	Jones	812	459	145	36
4	Wright	232	33	698	89
5	Bloom	10	56	211	101

<colgroup><col width="64" style="width: 48pt;" span="9"> <tbody> </tbody>

 

[Aladin Akyurek]

In B2 enter, copy across, and down:

=SUM(SUMIFS(INDEX(Sheet1!$B:$E,0,MATCH(B$1,INDEX(Sheet1!$B:$E,1,0),0)),Sheet1!$A:$A,$A2),
SUMIFS(INDEX(Sheet2!$B:$E,0,MATCH(B$1,INDEX(Sheet2!$B:$E,1,0),0)),Sheet2!$A:$A,$A2))