Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post

    Sumproduct formula in pivot table

    Masters,

    Good day!


    Is it possible to use sumproduct formula in pivot table?

    I've attached a template of my data showing computation in pivot using average and a sumproduct result using same problem. Just see sheets name (L4,L5 are sumproduct and REPORT sheet is pivot)

    Regards,

    Math
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No it isn't. A calculated field wouldn't work for this because it will sum the columns before performing any other operation, so if you create a column using:
    ='Tonnage w/ grade' * '%Fe'

    it will calculate as:
    =SUM('Tonnage w/ grade') * SUM('%Fe')

    rather than
    =SUM('Tonnage w/ grade' * '%Fe')

    which is what you want. Do you have access to Power Pivot?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Mathdarkmoon (2015-03-17)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Math

    As Rory says, you cannot have a sumproduct formula directly, but I believe you can achieve the same result using calculated fields together with some addition data columns in your table.
    I can get the sumproduct results you want in the pivot table using this method.
    See attached file.

    Your [Database] sheet has an error or glitch in cell [G2024].
    (This is why there is a #VALUE in my REPORT)

    NOTE: Although we can't post .xlsb files on this forum, I would recommend you use this Excel binary file format for you files.
    They will be considerable smaller and therefore faster to load, particularly on a network (and thus reducing network traffic).
    Try it.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Mathdarkmoon (2015-03-17)

  6. #4
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    @rory

    I want the option two if it is possible? Yes I have access with power pivot but don't know how to use it..


    @zeddy

    Thanks, I also found the error from the data... So this is the easiest way to have weighted average in pivot, It will help me a lot...


    Thanks to both of you..

  7. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you don't want to use Power Pivot, zeddy's solution is the simplest.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. The Following User Says Thank You to rory For This Useful Post:

    Mathdarkmoon (2015-03-17)

  9. #6
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    @rory

    If it will make the database easy and no more many formulas around, why not?

    Can you share how to do it in power pivot? I could easily follow instructions :-D

  10. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    OK. First you need to convert the Database range into a Table.
    Then I suggest you clean up your data columns so that they contain numbers only not text like "-".
    Then on the Power Pivot tab, click the Create Linked Table button, which will load your table in to Power Pivot.
    You can now add a calculated column in PP using a formula like:
    =[Tonnage w grade]*[Fe]
    (I then named the column "Weighted FE") and similarly for each column you want a weighted average for.
    Next, click the Pivot Table button on the Home tab of Power Pivot and choose where to put it (to select a range you need to click the grid button on the right of the dialog, otherwise you have to type the address in).
    Then you can add a measure by right clicking the table name in the Field List and use the formula:
    =sum(Table1[Weighted FE])/sum(Table1[Tonnage w grade])
    which will return the weighted average you want.

    It's effectively the same as what zeddy showed but all the calculations are done in PP rather than in the table on the worksheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #8
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    @rory

    Thanks for the info I'll have already made database based on your instructions but still I have questions..


    If I update my database does the database(pivot) will update automatically or I need to click refresh everytime database is updated

    Also I cannot add measure in the pivot, Tried your instruction but still got the wrong answer..


    Thanks and regards,

    Math

    PS: Is it allowed to post other uploader site link? I've used parse just incase it is now allowed.

    TemplateV2

  12. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I can't open that file - what version of Excel / Power Pivot are you using?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #10
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    I am using 64bit of MS and Power pivot before the latest released from MS (i think 2008R2 for pivot..)

  14. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    2010 or 2013?
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #12
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    Sorry I forgot to input it's 2010 sir...

  16. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I don't think I have 64bit 2010 installed anywhere. If I find the time I'll set up a VM to see what you have in that file.
    Regards,
    Rory

    Microsoft MVP - Excel

  17. #14
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    I already have the solution... Thank you!

    By the way, I have one question. If I update/input in the database, does the power pivot automatically update?

    Regards,

    Math

  18. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You have to refresh the pivot table but that's all.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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