|
Subtotal by 2 different criteria
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 |
Subtotal by 2 different criteria
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 DataSubtotalBy Supplier, with Sum function, columns to be totalled Amount, Replace subtotals checked DataSubtotalBy Month, with Sum function, columns to be totalled Amount, Replace subtotals UNCHECKED Regards, Stefi d7 ezt *rta: 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 |
| All times are GMT +1. The time now is 08:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com