+ Reply to Thread
Results 1 to 6 of 6

Sum and conditional transpose of Excel table data

  1. #1
    Registered User
    Join Date
    08-06-2015
    Location
    Nairobi, Kenya
    MS-Off Ver
    2007
    Posts
    3

    Sum and conditional transpose of Excel table data

    I have data that I want to summarize by sum of the quarters per year into department & year.
    I've also done the intended solution below the table data, need a formula to produce the same. I'm not really conversant with transpose/offset formulas and what I've tried so far is far from fruitful!
    Any help is welcome

    Year Quarter PL DP A&H
    2010 1 23 5 19
    2010 2 34 9 15
    2011 1 65 11 23
    2011 2 21 21 13

    Required solution should be as below

    PL 2010 57
    PL 2011 86
    DP 2010 14
    DP 2011 32
    A&H 2010 34
    A&H 2011 36

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum and conditional transpose of Excel table data

    Like this...

    Data Range
    A
    B
    C
    D
    E
    1
    Year
    Quarter
    PL
    DP
    A&H
    2
    2010
    1
    23
    5
    19
    3
    2010
    2
    34
    9
    15
    4
    2011
    1
    65
    11
    23
    5
    2011
    2
    21
    21
    13
    6
    ------
    ------
    ------
    ------
    ------
    7
    8
    9
    10
    PL
    2010
    57
    11
    PL
    2011
    86
    12
    DP
    2010
    14
    13
    DP
    2011
    32
    14
    A&H
    2010
    34
    15
    A&H
    2011
    36


    This formula entered in C10 and copied down:

    =SUMIF(A$2:A$5,B10,INDEX(C$2:E$5,0,MATCH(A10,C$1:E$1,0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    08-06-2015
    Location
    Nairobi, Kenya
    MS-Off Ver
    2007
    Posts
    3

    Re: Sum and conditional transpose of Excel table data

    Hey Tony that one great solution! Thanks
    Is there a way to do it if the data was in a pivot table since the second 2010 and second 2011 will be blank fields?

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum and conditional transpose of Excel table data

    I never use pivot tables so I'm not sure if/how it could be done.

  5. #5
    Registered User
    Join Date
    08-06-2015
    Location
    Nairobi, Kenya
    MS-Off Ver
    2007
    Posts
    3

    Re: Sum and conditional transpose of Excel table data

    See attached example
    Attached Files Attached Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum and conditional transpose of Excel table data

    Maybe add a new column to the pivot table for the year. I used column F.

    Data Range
    A
    B
    C
    D
    E
    F
    1
    2
    3
    Sum of GPW
    Column Labels
    4
    Row Labels
    Quarter
    PL
    DP
    A&H
    Year
    5
    2010
    1
    23
    5
    19
    2010
    6
    2
    34
    9
    15
    2010
    7
    2011
    1
    65
    11
    23
    2011
    8
    2
    21
    21
    13
    2011
    9
    10
    ------
    ------
    ------
    ------
    ------
    ------
    11
    12
    Solution required
    13
    14
    PL
    2010
    57
    15
    PL
    2011
    86
    16
    DP
    2010
    14
    17
    DP
    2011
    32
    18
    A&H
    2010
    34
    19
    A&H
    2011
    36


    Then the formula in C14 and copied down would be:

    =SUMIF(F$5:F$8,B14,INDEX(C$5:E$8,0,MATCH(A14,C$4:E$4,0)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How To Use The Transpose Function For My Data In Table
    By jezrp22 in forum Excel General
    Replies: 10
    Last Post: 03-10-2015, 03:41 PM
  2. transpose column to a range calculate and transpose to new table loop
    By moshro1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-02-2015, 10:52 AM
  3. Transpose pivot table/consolidate data?
    By saybut in forum Excel General
    Replies: 8
    Last Post: 04-03-2014, 02:30 PM
  4. [SOLVED] Conditional data transpose date wise
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-29-2013, 11:27 PM
  5. [SOLVED] Transpose table and sumif data
    By thefosh1984 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2013, 06:19 AM
  6. How to rotate a data Table in Excel 2010? (NOT TRANSPOSE)
    By jamonzonv in forum Excel General
    Replies: 1
    Last Post: 09-23-2013, 06:12 AM
  7. How to transpose data from 2 columns into a table?
    By wizzo in forum Excel General
    Replies: 2
    Last Post: 01-29-2008, 03:39 AM

Tags for this Thread

Bookmarks

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