Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Function
Hi,
I have a worksheet that contains about 1200 rows or more and 20 columns , out of which there are a number of groups ( say 34 groups ) carries it own sub-header. Under one group sub-header, it may have 2 or 10 or 50 or even 120 rows which distribute the data across 20 columns. Difficulty in data sorting as each group header has it own set of transactions based on their running dates. Thus, how to set a subtotal function for the specific columns under each group in this scenario. Example, A B C D H I J K L Date Trans. No Reference No Debit PO Cost None PO cost Travelling Accom Entertainment 61600.022 COC-04-001 & 04-002 19/03/2008 APD8-01024 00952001 1,258.00 1,258.00 21/03/2008 APD8-00990 9681 500.00 500.00 25/03/2008 APD8-01354 EKL-I-08/21121 47.93 47.93 10/04/2008 APD8-01218 3127 720.00 720.00 23/04/2008 GLP8-01411 MBB0804/4f-2 658.70 658.70 658.70 03/06/2008 GLP8-01950 MBB0806/1f-1 877.20 877.20 877.20 61620 COC-05-001 ABC Ltd 28/02/2008 GLP8-00682 MBB036759(P) 110.00 110.00 110.00 61637 COC-05-015 XYZ plc 01/01/2008 APD8-00627 50043 9,366.65 9,366.65 09/01/2008 APD8-00121 6741 952.00 952.00 09/01/2008 APD8-00214 9610 120.00 120.00 61638 COC-05-020 BBCC ltd 01/01/2008 APD8-00628 I0712012 306.00 306 01/01/2008 APD8-00629 4075 207.71 207.71 01/01/2008 APD8-00631 4087 20,428.72 20428.72 08/01/2008 APD8-00038 NIV-08010306 47.00 47 08/01/2008 APD8-00300 EKL-I-08/20833 174.11 174.11 09/01/2008 APD8-00079 UI801092 482.40 482.4 12/01/2008 APD8-00004 KUL690158 294.21 294.21 18/01/2008 APD8-00332 CTIV00010692 1,377.00 1,377.00 1,377.00 1,377.00 21/01/2008 GLP8-00218 MBB0801/3a-1 8,660.90 8,660.90 8,660.90 8,660.90 31/01/2008 APD8-01178 INV002065227 2,100.00 2100 31/01/2008 JNL08-0016 JNL07-0491 - 12/02/2008 APD8-00680 CTIV00011406 235.00 235.00 235.00 235.00 15/02/2008 APD8-00376 VE8P0095 1,165.50 1165.5 15/02/2008 GLP8-00686 MBB0802/1b-1 283.25 283.25 - 283.25 15/02/2008 GLP8-00686 MBB0802/1b-1 665.30 665.3 665.3 665.3 In the above scenario, the subtotal function for column H to L under the following group sub-header will be : - a) Column H I J L 61600.022 COC-04-001 & 04-002 2,525.93 1,535.90 1,535.90 0.00 b) 61620 COC-05-001 ABC Ltd 0.00 110.00 110.00 0.00 c) 61637 COC-05-015 XYZ plc 10,438.65 0.00 0.00 0.00 d) 61638 COC-05-020 BBCC ltd 25,205.65 11,221.45 10,938.20 283.25 Please help how to set subtotal function for the above scenario, thanks Regards Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal function not available | Excel Worksheet Functions | |||
Subtotal Function | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
Subtotal function | New Users to Excel | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions |