% of Accumulated sub-total in Pivot table

[Lynn Au]

Dear All

I have a problem about displaying a % of accumulated sub-total value in pivot table,
now the pivot table only shows the % of the accumulated grand total.

Please open my attached file for the data source and the pivot table, as well as the ideal pivot table I need.

If this problem had been answered, please let me know. I can refer to the old thread. I tried to search before I make this message, but the results were too many and seem not relevant to my question.

I use Excel 2003.

Thank you.

[Pauleyb]

I am not an expert on pivot tables, but I played around with it and could not create what you were looking for in through a pivot table. So, I would recommend creating the numbers you want in your source table and then adding it to the pivot table.

In cell E1 give your column a title

In your Data sheet in cell E2:
=D2/SUMIFS($D$2:$D$55,$A$2:$A$55,A2,$B$2:$B$55,B2)

I think SUMIFS is not available in Excel 2003, so you may have to use this:
=D2/SUMPRODUCT($D$2:$D$55*($A$2:$A$55=A2)*($B$2:$B$55=B2))

Note that the last formula is an array formulay, so you will need to type/paste it into cell E2 and then hit ctrl-shift-enter.

Drag the formula in E2 down to the end of your table.

[Lynn Au]

Dear Pauley

Thanks so much for offering help.

I tried the formula, but I also could not get the ideal result I want.

The reason is I need the pivot table to calculate the accumulated sum and
the corresponding percentage.

I take Singapore apple as an example. In Jan, apple sold 85, in Feb, 174;
total sold in Jan is 671, Feb 568. Pivot table shows 259 for apple, 1239
for all total respectively under the column of Feb. The percentage I expect
is 85/671 in Jan, 259/1239 in Feb, and so for other months. Denominator
will be changed from month to month.

The second problem is the real data source is bigger than this example. If
I use this formula, the worksheet will re-calculate the whole thing again when
I use filter, it takes a great deal of time.

Anyway, I appreciate your help. You showed me another formula function I
did not know before. Thank you.

[Pauleyb]

Okay, the formula you want in your table is:
=SUMPRODUCT($D$2:$D$55*($C$2:$C$55=C2)*($B$2:$B$55=B2)*($A$2:$A$55<=A2))/SUMPRODUCT($D$2:$D$55*($B$2:$B$55=B2)*($A$2:$A$55<=A2))

Since you have Excel 2003, it would continue to be the array formulae. As you state, the array formulae can be time consuming when you have a lot of data, so those won't appear to work for you. =SUMIFS() should be faster, but I believe that showed up in Excel 2007.

[Lynn Au]

Dear Pauley

I am sorry the formula does not work.
Or will you work it in the file and display the pivot table for me.
I guess I might not know how it works in the pivot table.

[Pauleyb]

It is sometimes problematic to change pivot tables from 2007 to 2003, but I think this will open and work for you.

[Lynn Au]

Dear Pauley

The 2007 pivot table cannot be displayed properly in 2003.
I need to open it at home using 2010. Finally I saw the file
and got idea how to apply in 2003. It worked!

However, it does not help to resolve my real problem at work.
Firstly, my data source is bigger than my example;
second, not every item bears data. For example, Hong Kong might
not have sales result for apple in March. Then the % calculation
for apple of Hong Kong in March becomes blank. The % calculation
in April, May and June will become incorrect.

I guess I have to give up. It might be a dead-end problem for
excel!

Finally, I think I will appreciate your help with proper action
you mentioned before first, but I will have this problem unsolved.