Summarising Data with a Pivot Table and Formulae

[andrewc]

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!!!