Subtotal by 2 different criteria

[d7]

Sorry didnt explain my last post very well, on the sheet i have it is about
10,000 lines with around 300 different suppliers, what im after is an
extension to the normal subtotal, I can do a subtotal at each change in
supplier to give me the overall total underneath each supplier, but within
that i need a subtotal under each change in month.
List A shows the kind of data i have as an example (but this list is 10,000
lines and 300 suppliers)
List B is what im after as a result

LIST A
Supplier Date Amount
ste01 01/01/2009 1
ste01 06/01/2009 45
ste01 10/01/2009 1558
ste01 01/02/2009 15
ste01 17/02/2009 15
ste01 18/02/2009 48
ste01 19/02/2009 57
ste01 20/02/2009 2
zed01 01/01/2009 37
zed01 02/01/2009 41
zed01 06/01/2009 42
zed01 25/02/2009 4
zed01 26/02/2009 24
zed01 01/03/2009 2
zed01 02/03/2009 7
zed01 03/03/2009 75
zed01 04/03/2009 58

LIST B
Supplier Date Amount
ste01 01/01/2009 1
ste01 06/01/2009 45
ste01 10/01/2009 1558
Subtotal Jan09 - 1604
ste01 01/02/2009 15
ste01 17/02/2009 15
ste01 18/02/2009 48
ste01 19/02/2009 57
ste01 20/02/2009 2
Subtotal Feb09 - 137

Overall Total ste01 - 1741
zed01 01/01/2009 37
zed01 02/01/2009 41
zed01 06/01/2009 42
Subtotal Jan09 - 120
zed01 25/02/2009 4
zed01 26/02/2009 24
Subtotal Feb09 - 28
zed01 01/03/2009 2
zed01 02/03/2009 7
zed01 03/03/2009 75
zed01 04/03/2009 58
Subtotal Mar09 - 142
Overall Total zed01 - 290

 

[Stefi]

Use a helper column, say D, enter header text (say Month) in D1, formula in D2:
=TEXT(B2,"mmmyy")
fill it down to the last row,
select A1
Data>Subtotal>By Supplier, with Sum function, columns to be totalled Amount,
Replace subtotals checked
Data>Subtotal>By Month, with Sum function, columns to be totalled Amount,
Replace subtotals UNCHECKED

Regards,
Stefi


„d7†ezt írta: