Sumifs where columns are transposed and contain duplicate labels

[drluke]

How can I do a SUMIFS (or similar) where columns/rows are transposed and columns have the same label (like B&D below). In my data sheet data is organised like:

	3100	3587	3100	3800
Talent	200	320	124	256
Sourcing	90	77	85	784
Media	45	69	30	941
Talent	426	25	24	4256
Sourcing	497	74	85	287
Media	569	36	26	365

<tbody>
</tbody>

In my 2nd worksheet the columns/rows are transposed but I need to be able to sum the total of all columns including combining col B & D (3100) for Sourcing etc. without the need to change/add anything to the datasheet. Eg the total for Sourcing/3100 = 757

	Talent	Sourcing
3100
3587

<tbody>
</tbody>

Any advise much appreciated.​

 

[Aladin Akyurek]

	3100	3587	3100	3800			Talent	Sourcing
Talent	200	320	124	256		3100	626	587
Sourcing	90	77	85	784		3587	345	151
Media	45	69	30	941
Talent	426	25	24	4256
Sourcing	497	74	85	287
Media	569	36	26	365

<tbody>
</tbody>

In H2 enter, copy across, and down:

=SUMIFS(INDEX($B$2:$E$7,0,MATCH($G2,$B$1:$E$1,0)),$A$2:$A$7,H$1)

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​		3100​	3587​	3100​	3800​		sheet1
2​	Talent	200​	320​	124​	256​
3​	Sourcing	90​	77​	85​	784​
4​	Media	45​	69​	30​	941​
5​	Talent	426​	25​	24​	4256​
6​	Sourcing	497​	74​	85​	287​
7​	Media	569​	36​	26​	365​

<tbody>
</tbody>

	A​	B​	C​	D​	E​
1​		Talent	Sourcing		sheet2
2​	3100​	774​	757​
3​	3587​	345​	151​

<tbody>
</tbody>

sheet2

B2=SUMPRODUCT((Sheet1!$B$1:$E$1=Sheet2!$A2)*(Sheet1!$A$2:$A$7=Sheet2!B$1)*(Sheet1!$B$2:$E$7)) copy down and across