Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » Pivot Tables » Subtotaling Index Pivot Tables

Subtotaling Index Pivot Tables

Previous | Next
Latest post Thu, Jul 24 2008 1:40 PM by That Excel Guy. 3 replies.

  • Tue, Jul 22 2008 3:15 PM

    Subtotaling Index Pivot Tables

    Reply |Contact

    Hi Everyone - I have a stumper that several other boards did not have an answwer for, and I hope that someone can help.

    I have to run several pivots to fill a crystal xcelsius excel input model - two of them count partner company names and user IDs by region and country via the index function.

    What I want to know is once the pivot has been pasted with special values (with the subtotal fields color coded before the PSV), would it be possible to find a way to subtotal each country through a formula?  The problem is that the data varies from country to country, so the number of companies or user IDs is not stable, but I thought it might be able utlize the color coding some how (or some other method). 

    Anyone have any ideas? 
    • Post Points: 21

  • Thu, Jul 24 2008 7:53 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 58
    • Points 976
    • MVP

    Re: Subtotaling Index Pivot Tables

    Reply |Contact |Mark as Answer

     Hi
    One way would be to use a Function to return the colour of the cells in a column to the side of your extracted PT Data

    Function GetColour(cell As Range) As Long
     GetColour = cell.Interior.ColorIndex
    End Function
    In a column to the right of your PT data, enter something like
    =Getcolour(C5)
    and copy down as far as required.
    Assuming this had been entered in M5 through M50 then use a Sumproduct formula like
    =SUMPRODUCT((M5:M50=your_colour)*C5:C50)
    substituting the colour index of the colour you want for your_color e.g. 6 for Yellow
    To utilise the function, copy the 3 lines of code pasted
    Alt+F11 to invoke the VB Editor
    Insert> Module
    Paste the code into the whire pane that appears
    Alt+F11 to return to Excel
    If you amend the colours, press F9 to get the function to recalculate.
    Regards Roger Govier Microsoft Excel MVP
    • Post Points: 5

  • Thu, Jul 24 2008 9:20 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 58
    • Points 976
    • MVP

    Re: Subtotaling Index Pivot Tables

    Reply |Contact |Mark as Answer

     Of course, having obatined the interior colour of the cells, you could use the much faster SUMIF function, rather than SUMPRODUCT

    =SUMIF(M5:M50,your_colour,C5:C50)

    Regards Roger Govier Microsoft Excel MVP
    • Post Points: 21

  • Thu, Jul 24 2008 1:40 PM In reply to

    Re: Subtotaling Index Pivot Tables

    Reply |Contact |Mark as Answer

     Thanks Roger - this sounds like an interesting solution.  However, realistically, I want the totals for each country, the region, and the grand total, so I think I need a different type of logic (without cell references).

    If I were to word it out in plain English, I guess it would look somthing like this:

    (I will manually set the sum for the first country), THEN

    1.  Find yellow cell

    2.  Sum cells above yellow cell (checking upwards until last summed cell is found) and place total in cell

    3.  Repeat process for next  yellow cell found underneath

    Do this make sense?
    • Post Points: 5
Page 1 of 1 (4 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.