+ Reply to Thread
Results 1 to 3 of 3

Pivot Table with all field combination subtotals

  1. #1
    Registered User
    Join Date
    09-05-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    2

    Pivot Table with all field combination subtotals

    I have a number of summary tables that are split by various fields. For reporting, I need to produce the subtotals of every combination of field values. For example, consider the table below (all following tables can be found in the attached document):

    RawTable.png

    I would need to produce the following summaries:

    SummaryTable.png

    Using a single PivotTable, I can put Month into the column fields and Var1 and Var2 into the row fields which would produce most of the subtotals, but it would not produce, say, the split by Var2 only (X = 585, Y = 1170, Z = 2340). The PivotTable is below.

    PivotTable.png

    Is there a way to get the PivotTable to include all of the subtotal for all of the combinations of fields?

    Note that I am specifically avoiding the SUMIF and SUMPRODUCT type functions, as I need to produce tens of thousands of these summaries (the fields have a lot of segments) and the calculations take far too long to be feasible, even when restricting the arrays to only non-blank rows.
    Attached Files Attached Files
    Last edited by Bilbottom; 09-05-2019 at 07:33 AM. Reason: Added attachment

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,625

    Re: Pivot Table with all field combination subtotals

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-05-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Pivot Table with all field combination subtotals

    Hi Richard,

    Thanks, and apologies for the original poor formatting -- I am still getting used to navigating this site.

    I have replaced the text tables with pictures, and also included the workbook as an attachment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 07-01-2019, 08:01 AM
  2. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  3. Pivot Table Calculated Field Showing Differences of Subtotals
    By jsmith1061 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-24-2016, 02:34 PM
  4. [SOLVED] Subtotals for a calculated field within a Pivot table
    By marketshare in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-15-2013, 02:45 AM
  5. Replies: 0
    Last Post: 03-02-2013, 08:15 AM
  6. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  7. Replies: 3
    Last Post: 03-03-2012, 12:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1