Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables: Combining data from multiple columns (Excel 2003)

    I have a spreadsheet with the following columns: Product, Sales 1, Sales 2, Sales 3 and Sales 4
    A person can either be the primary salesperson (Sales 1), secondary salesperson (Sales 2) or additional salesperson (Sales 3 and 4).
    I want to show how many of Product A, B, C etc a particular salesman has sold, irrespective of whether he was Sales 1, 2, 3 or 4. For example, Jim sold product A twice - once as Sales 1 and once as Sales 3. I want the pivot table to simply show that Bob sold 2 of Product A.

    I can't seem to get the pivot table to show the correct information.

    I have attached a sample spreadsheet - if anyone out there has any suggestions, I'd be very grateful!

    Thanks,

    JoeK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables: Combining data from multiple columns (Excel 2003)

    With the present table structure, it isn't easy to create a pivot table. You could use SUMPRODUCT formulas instead - see Sheet1 in the attached workbook.
    You could use a more database-like table structure to serve as source for a pivot table - see Sheet2 in the attached workbook.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables: Combining data from multiple columns (Excel 2003)

    Thanks, Hans.

    The SUMPRODUCT formula worked a treat.

    Much appreciated,

    JoeK

Posting Permissions

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