Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Sum Query (Excel 2002)

    Hi

    I have a spreadsheet which includes identical products in seperate periods (see attached) I would like to sum the diff column for all the products in each period but only see the product name just once for each product . is this possible please.

    Thanks in advance.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum Query (Excel 2002)

    Create pivot table.
    DAta - pivot table report

    row field is product col
    column field (or perhaps a 2md row field) is period col
    data field is sum of the diff col

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Sum Query (Excel 2002)

    Hi Steve

    This doesn't really work for me because it still shows multiples of the same product, what I would like to see is a complete column of all the products ie maybe 127 rows each containing a sinlge product but with the adjacent cell showing the total for all the periods, if this is possible.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Query (Excel 2002)

    Maybe I'm reading your post wrong, but it sounds to me like you want to use subtotal on each change in Product. Would that give you what you're looking for?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum Query (Excel 2002)

    Then how about this pivot table.

    Data - pivot table report
    row field is product col
    No column field
    data field is sum of the diff col

    If this is not what you are after, could you attach a sample sheet with what you have, and a sheet of what you want, since it is not clear to me.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Sum Query (Excel 2002)

    Hi Ban

    That exaclty what I am looking for, can you explain how i would do this please.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sum Query (Excel 2002)

    Data -subtotals
    At each change in: product
    Use function: Sum
    Add subtotal to: Diff
    <ok>
    After the subtotals are added, click on the "2" in the left "border top" to "hide the values" only keep the subtotals

    Steve

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Sum Query (Excel 2002)

    Hi Steve

    Thats what I needed thanks to everyone who responded to this post.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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