# Sumifs where columns are transposed

#### drluke

##### Active Member
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>

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### macattackpro

##### Board Regular
Hello,

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

Code:
``{=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

##### MrExcel MVP
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

##### Well-known Member
 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>

Replies
2
Views
262
Replies
8
Views
48
Replies
9
Views
287
Replies
31
Views
980
Replies
2
Views
103

1,141,480
Messages
5,706,638
Members
421,460
Latest member
Taamrak

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back