Results 1 to 2 of 2

Thread: Sumproduct in Pivot Tables - can it be done?

  1. #1
    Neophyte TODAW's Avatar
    Join Date
    Nov 2018
    Posts
    1
    Articles
    0
    Excel Version
    2013

    Sumproduct in Pivot Tables - can it be done?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I'm looking to create a pivot table (or hell, I'll take a straight spreadsheet formula) that utilizes sumproducts, or a variation formula of, that will allow me to filter out data and automatically update my sumproduct to exclude the filtered data. In the chart below, to get the data I need [which, the main detail I need is C5 which would have the calculation of sumproduct(B1:B4,C1:C4)/B5 ] I would want to be able to filter out Product Y and get an updated weighted average based on the remaining data.

    I have the below formula, which works fine when it comes to a straight total( for example, in the chart I provided the reviews come to a total of 20941) however this doesn't work for the data I need when a sumproduct is in play (such as the 88.99)
    =SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,{0;1;2;3},)),$B$2:$B$5)

    Any direction that can be provided would definitely be appreciated.


    Product (A) Reviews (B) Feedback (C)
    1. W 1891 85.43
    2. X 4730 88.25
    3. Y 6876 89.09
    4. Z 7444 90.27
    20941 (TOTAL) 88.99 (Sumproduct)

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    =SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,{0;1;2;3},)),$C$2:$C$5)/B6
    should work as long as you adjust the B6 total cell to use a SUBTOTAL formula.
    This website wants to know your momentum - | Deny | | Allow |

Tags for this Thread

Posting Permissions

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