# Transpose table and sumif data

1. ## Transpose table and sumif data

Hi, I have the following table:
2AA 2AB 2AC 2AD 2AE
Finance 100 10 100 10 100
HR 200 20 200 20 200
ICT 300 30 300 30 300
Finance 400 40 400 40 400
HR 500 50 500 50 500
ICT 600 60 600 60 600
Finance 700 70 700 70 700
HR 800 80 800 80 800
ICT 900 90 900 90 900

and I need to take the data and transpose the headings but then get the total of each as follows:
Finance ICT HR
2AA 1200 1800
2AB 120
2AC
2AD
2AE

Is there a simple way of doing this? I have attched the excel spreadsheet if that helps.

thanks

2. ## Re: Transpose table and sumif data

Please try this function

Across C3:E7

=SUMPRODUCT((\$B\$19:\$B\$27=C\$2)*(\$C\$18:\$G\$18=\$B3)*(\$C\$19:\$G\$27))

3. ## Re: Transpose table and sumif data

That is brilliant ramananhrm....that worked perfectly. Thank you

4. ## Re: Transpose table and sumif data

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1