Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Working with Lists and summing data

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all,
    I am looking for a few pointers as to the best way of dealing with this situation.
    Say:
    Col.A, Col.B, Col.C
    Material, Qty, Rate
    Cement, 1, 3.00
    Sand, 5, 2.50
    Cement, 3, 2.50
    Cement, 3, 3.00

    I would like to total all the Cement, Sand and whatever else may appear but I wish them to be totalled also within discreet Rates.
    ie:

    Cement, 4, 3.00
    Cement, 3, 2.50
    Sand, 5, 2.50

    I am not aware of which headings will appear nor rates and quantities.
    I have contemplated Data, Consolidate - SubTotals and Pivot Tables ... any other suggestions?
    These 'summary results' are then to be copied elsewhere on the worksheet.
    I trust their is someone out there to help me in the right direction.
    Cheers
    S.

    Sorry the Columns looked really pretty when I typed it out .. honest!

    [ This Message was edited by: Sam40mUK on 2002-04-03 10:28 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How 'bout using a PivotTable with 'Material' and 'Rate' in the ROW area and 'Sum of Qty' in the DATA area.

    ******>
    Sum of Qty
    MaterialRate Total
    Cement2.503
    3.004
    Sand2.505



    [ This Message was edited by: Mark W. on 2002-04-03 10:38 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Mark,
    Thanks for the swift response.
    I have tried your suggestion and it seems to work a treat.
    For future refernce is a PT the only way of going about this kind of thing?
    Cheers again.
    S.

    [ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Another alternative is SumProduct
    I used range names but that is not necesary.
    You can extend the totals if you want with

    =Qty*Rate

    Grand total is =SUMPRODUCT((Qty*Rate))

    With categories cement and sand in A9 and A10, the following gives total by category

    =SUMPRODUCT((Material=A9)*(Qty)*(Rate))
    =SUMPRODUCT((Material=A10)*(Qty)*(Rate))

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-03 10:57, Sam40mUK wrote:
    Hello Mark,
    Thanks for the swift response.
    I have tried your suggestion and it seems to work a treat.
    For future refernce is a PT the only way of going about this kind of thing?
    Cheers again.
    S.

    [ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]
    I wouldn't be so bold as to claim that a PivotTable is the only way, but it certainly is an easy way.

    [ This Message was edited by: Mark W. on 2002-04-03 12:22 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 12:22, Mark W. wrote:
    On 2002-04-03 10:57, Sam40mUK wrote:
    Hello Mark,
    Thanks for the swift response.
    I have tried your suggestion and it seems to work a treat.
    For future refernce is a PT the only way of going about this kind of thing?
    Cheers again.
    S.

    [ This Message was edited by: Sam40mUK on 2002-04-03 11:00 ]
    I wouldn't be so bold as to claim that a PivotTable is the only way, but it certainly is an easy way.

    [ This Message was edited by: Mark W. on 2002-04-03 12:22 ]
    I definitely like the answer.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    North Wales Coast, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx for all your replies I see the way a little clearer now
    Cheers
    S.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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