Summarising Data with a Pivot Table and Formulae

Hi,

In columns H to N of the attached worksheet I have sample data for a number of products that I would like to summarise according to their Region, Country and City of origin. The data in L:M is the percentage of total sales of the business represented by each product, the growth rate of sales for each product and the percentage contribution of each product to the total company sales.

The summary I would like to achieve is shown in A:D.

Maintaining this summary is quite cumbersome because new products may be added or removed frequently which may also mean that new region, country and city headings are required or need to be removed.

To make the maintenance of the summary less time-consuming, is it possible to reproduce this table using a Pivot Table? If so, how would one get the PivotTable to calculate the growth rates for the region, country and city? (see the summary formulae in column C; all this is doing is, for example, calculating the growth rate of products in Birmingham according to their percentage of total sales in that city).

Can someone please suggest how to create such a Pivot Table?

Thanks very much!!!

Thanks very much, Richard

Growth rate cannot be calculated in pivot considering the formula complexity.

You can manually add formula at the end of pivot and it will work normally if you are not going to add any new region or country. But in case you wish to add you need to alter the formula little bit.

I have added formula in file sent by Richard.

Rupesh