subtotal - multiple criteria

[mdma]

i have a sheet, which i want to sub total on two levels..

for example, i have a product number, country code, and value. Th
sheet is sorted by product number then country code.

i require totals for the value of the product (which i can acheive b
using the sub total function), but also require a further total fo
upon change in country code with the the product number :

product number country value
1 a 5
1 a 10
*subtotal 15*

1 b 15
*subtotal 15*

Total 1 30

2 c 20
*subtotal 20*

Total 2 2

 

[Guest]

Sounds like the perfect place to use a Pivot table

Data->Pivot table and Pivot Chart Report...

Offers a variety of sorting and subtotaling options without messing with
your original data

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Debra Dalgleish]

You can add another level of subtotals:

Sort the list by Product number and Country
Subtotal the list at change in Product Number
Choose Data>Subtotals again
Select change in Country
Remove the check mark from 'Remove current subtotals'
Click OK

 

[mdma]

thanks for your replies..

i have over 6000 thousand lines with approx 800 items, so the
sumproduct will not be the easiest method?

I did think of pivot tables, but i have other details on each row which
i still want to see (customer name) - so with the pivot table this will
lose this information and just result in totals.

The re-apply of the subtotals seems easy and more suitable, which i
have tried. This does work, however, where i have an item with more
than one country, the last entry does not "appear" correctly.. I will
get the grand/sub total for the item, and then under this i will get
the country total. Although they are correct, visually it looks
misleading. Are there any steps i can take to correct this?

Again, thanks for your replies

 

[Debra Dalgleish]

There's information in the following MSKB article:

Multilevel subtotals are in the wrong position in Excel 2002 and in
Excel 2003
http://support.microsoft.com/kb/831824/

 

[mdma]

Thank you SOOOO much!

Quick reg edit and all is cured. spent most of today doing what
turned out to be a 10 minute job.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that the registry edit fixed
the problem.

Thank you SOOOO much!

Quick reg edit and all is cured. spent most of today doing what
turned out to be a 10 minute job.