Sub - Subtotals

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.

Hi,

I think you might consider SUMIF(Product,"Electronic",Amount)

In this example Product represents the range of the product names, and
Amount represents the name of the Amounts range. You can use cell references
instead. Also if you enter Electronic in cell H1 for example then you can
change the formula to read SUMIF(Product,H1,Amount) This way you could list
all the Products in cells H1:H10 and then copy the formula down, wherever you
choose to put it.

--
Cheers,
Shane Devenshire


"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.

"ShaneDevenshire" wrote:

Hi,

I think you might consider SUMIF(Product,"Electronic",Amount)

In this example Product represents the range of the product names, and
Amount represents the name of the Amounts range. You can use cell references
instead. Also if you enter Electronic in cell H1 for example then you can
change the formula to read SUMIF(Product,H1,Amount) This way you could list
all the Products in cells H1:H10 and then copy the formula down, wherever you
choose to put it.

--
Cheers,
Shane Devenshire


"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.

Thks for the sumif function which works out fine, but i still need to report
the sales by rpduct type with shopAA and ShopBB whic make up the Total by
Shops which in turn add up to make up the Grand Total.

The actual data contains more than one hundred such products.

"ShaneDevenshire" wrote:

Hi,

I think you might consider SUMIF(Product,"Electronic",Amount)

In this example Product represents the range of the product names, and
Amount represents the name of the Amounts range. You can use cell references
instead. Also if you enter Electronic in cell H1 for example then you can
change the formula to read SUMIF(Product,H1,Amount) This way you could list
all the Products in cells H1:H10 and then copy the formula down, wherever you
choose to put it.

--
Cheers,
Shane Devenshire


"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.

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.

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.