Thank you very much indeed. This is exactly what I was looking for


"smartin" wrote:

WINS wrote:
Can someone please help me out?

I currently have a list of on which I currently have subtotals based on shop
Name, this gives me revenue by shops and total revenues. I am now required to
further show revenue by product type.

Example is set out below;

Shop Product Salesman Amount
AA Electrical SP1 10.00
AA Mechanical SP2 5.00
AA Electrical SP1 10.00
AA Total 25.00

BB Repairs SP4 10.00
BB Electronic SP4 20.00
BB Electronic SP4 10.00
BB Total 40.00

Grand Total 65.00

I would in the present scenario need a subtotal for Electrical and one for
Mechanical within subtotal for Shop AA and same thing for Repairs and
Electronic within subtotal for Shop BB.

Looking forward to receive your bright ideas and possible workaround.

Two ways come to mind:

Subtotals function: First, sort the untotaled list by Shop (primary),
Product (secondary). Select the data range and use Data | Subtotals. Set
this up by choosing At Each Change in Shop, Sum, check Amount, Add to
Amount.

Next, repeat Data | Subtotals. This time, At Each Change in /Product/,
Sum, Amount, Add to Amount. *Uncheck* "Replace current subtotals" and
click OK. Now you have subtotals by Product within Shop, and Subtotals
by Shop.



Pivot table: select the untotaled list (doesn't matter if it's sorted),
and use Data | Pivot table. Finish with the defaults. Now drag Shop and
Product into the Row Area (you may need to rearrange the buttons so
Product is to the right of Shop, just drag them around if necessary).
Last, place Amount in the Data area. Now you have subtotals by Product
within Shop, and Subtotals by Shop.

If you would like to see subtotals by both Product and Shop both
dependently (as in the examples above) and independently, use the pivot
table and simply drag the Shop button from the Row are to the Column Area.