Sumifs where columns are transposed

[drluke]

How can I do a SUMIFS (or similar) where columns/rows are transposed. In my data sheet data is organised like:

	3100	3587	2568	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 3100 for Sourcing etc.

	Talent	Sourcing
3100
3587

<tbody>
</tbody>

Any advise much appreciated.

 

[macattackpro]

Hello,

Assuming your data is on 'Sheet1' and your product is on 'Sheet2', try this for 'Sourcing' and '3100' (Cell C2):

{=SUM(IF(Sheet1!$A$2:$A$7=C$1,IF(Sheet1!$B$1:$E$1=$A2,Sheet1!$B$2:$E$7)))}

Note that the formula is entered with CTRL + SHIFT + ENTER to make it an array formula.

 

[Marcelo Branco]

Assuming your data in Sheet1 A1:E7 (adjust to suit) try something like this

Sheet2

	A​	B​	C​
1​		Talent​	Sourcing​
2​	3100​	626​	587​
3​	3587​	345​	151​

Formula in B2 copied across and down
=SUMIF(Sheet1!$A$2:$A$7,B$1,INDEX(Sheet1!$B$2:$E$7,0,MATCH($A2,Sheet1!$B$1:$E$1,0)))

M.

 

[oldbrewer]

	col B
							col H
	code3100	code3587	code2568	code3800
Talent	200	320	124	256				3100	3587	2568	3800		row 4
Sourcing	90	77	85	784			talent	626	345	148	4512
Media	45	69	30	941			sourcing	587	151	170	1071
Talent	426	25	24	4256
Sourcing	497	74	85	287			very easy to use sumproduct to make the above table
Media	569	36	26	365	row 9
							then use offset match to pull the values into your desired table layout
							626 ABOVE obtained by
	Talent	Sourcing					=SUMPRODUCT((B4:B9)*($A4:$A9=$H5))
3100	626	587
3587	345	151
3800	4512	1071					626 LEFT obtained by
2568	148	170					=OFFSET($H$4,MATCH(B$13,$H$5:$H$6,0),MATCH($A14,$I$4:$L$4,0))

<colgroup><col><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>