1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table with more than one grand total

Discussion in 'Ask an Excel Question' started by Costas, Jul 12, 2018.

  1. Costas

    Costas Member

    Messages:
    71
    Hi Chandoo,

    I've set up a pivot table to compare sales by salesperson actual vs budget (see attached photo).
    I have two issues:
    • The grand total adds actual and budget values into something that is meaningless to me.
    • The variance % does not show in the grand total but I guess if we fix the first point the second would solve itself.
    Thanks
    Costas

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    212
    Please post a sample sheet with some data and expected results (pics are nice but allas useless)
  3. Costas

    Costas Member

    Messages:
    71
    Hi pecoflyer,

    Please see attached excel file. The expected results tab shows what I'd like to see in the pivot table tab.

    The main difference is in the grand totals showing totals for actual, budget and variance.

    Thanks
    Costas

    Attached Files:

  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    780
    perhaps, like this...
    upload_2018-7-14_18-46-22.png
    upload_2018-7-14_18-46-45.png
    Costas likes this.
  5. Costas

    Costas Member

    Messages:
    71
    Hi GraH - Guido,

    This is what I'm after in the grand totals but I lose the setup I had for each salesperson as per my expected results tab.

    Thanks anyway
    Costas
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,908
    Maybe,

    1] Add 2 rows behind "Grand Total" for "Actual" and "Budget"

    2] In B31, copied down to B32, then copied/paste to D31:D32 and F31:F32

    =SUMPRODUCT(($A$7:$A$28=$A31)*B$7:B$28)

    3 In C31, copied/paste to E31 and G31

    =(B31-B32)/B32

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page