i have a sheet, which i want to sub total on two levels..
for example, i have a product number, country code, and value. The sheet is sorted by product number then country code.
i require totals for the value of the product (which i can acheive by using the sub total function), but also require a further total for 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 20
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
thanks for your replies..
i have over 6000 thousand lineswith 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
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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.
Originally Posted by 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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> /snip
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="a"),C2:C100)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"mdma" <[email protected]> wrote in message
news:[email protected]...
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
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
"mdma" wrote:
>
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
> --
> mdma
> ------------------------------------------------------------------------
> mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
> View this thread: http://www.excelforum.com/showthread...hreadid=396421
>
>
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 wrote:
> i have a sheet, which i want to sub total on two levels..
>
> for example, i have a product number, country code, and value. The
> sheet is sorted by product number then country code.
>
> i require totals for the value of the product (which i can acheive by
> using the sub total function), but also require a further total for
> 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 20
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
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 wrote:
> thanks for your replies..
>
> i have over 6000 thousand lines
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
You're welcome! Thanks for letting me know that the registry edit fixed
the problem.
mdma wrote:
> 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 Wrote:
>
>>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 wrote:
>>
>>>thanks for your replies..
>>>
>>>i have over 6000 thousand lines
>>>/snip
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks