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:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]3100[/TD]
[TD]3587[/TD]
[TD]2568[/TD]
[TD]3800[/TD]
[/TR]
[TR]
[TD]Talent[/TD]
[TD]200[/TD]
[TD]320[/TD]
[TD]124[/TD]
[TD]256[/TD]
[/TR]
[TR]
[TD]Sourcing[/TD]
[TD]90[/TD]
[TD]77[/TD]
[TD]85[/TD]
[TD]784[/TD]
[/TR]
[TR]
[TD]Media[/TD]
[TD]45[/TD]
[TD]69[/TD]
[TD]30[/TD]
[TD]941[/TD]
[/TR]
[TR]
[TD]Talent[/TD]
[TD]426[/TD]
[TD]25[/TD]
[TD]24[/TD]
[TD]4256[/TD]
[/TR]
[TR]
[TD]Sourcing[/TD]
[TD]497[/TD]
[TD]74[/TD]
[TD]85[/TD]
[TD]287[/TD]
[/TR]
[TR]
[TD]Media[/TD]
[TD]569[/TD]
[TD]36[/TD]
[TD]26[/TD]
[TD]365[/TD]
[/TR]
</tbody>[/TABLE]

In my 2nd worksheet the columns/rows are transposed but I need to be able to sum the total of 3100 for Sourcing etc.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]Talent[/TD]
[TD]Sourcing[/TD]
[/TR]
[TR]
[TD]3100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3587[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

Talent​

[/td][td]

Sourcing​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

3100​

[/td][td]

626​

[/td][td]

587​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3587​

[/td][td]

345​

[/td][td]

151​

[/td][/tr]
[/table]

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]

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