| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
total
Dear All
Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID & date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc. Thanks Remarks my data not include any empty raw Working data as below Date Emp #.ID No. of Hours 01/03/10 116 4.00 01/03/10 116 2.00 01/03/10 116 5.00 01/03/10 116 5.00 03/03/10 116 11.00 04/03/10 116 11.00 28/03/10 116 11.00 01/03/10 120 2.00 01/03/10 120 5.00 01/03/10 120 4.00 02/03/10 120 9.00 02/03/10 120 2.00 03/03/10 120 11.00 04/03/10 120 11.00 23/03/10 120 11.00 25/03/10 120 6.00 25/03/10 120 5.00 01/03/10 131 11.00 02/03/10 131 11.00 03/03/10 131 6.00 03/03/10 131 5.00 16/03/10 131 11.00 25/03/10 131 2.00 25/03/10 131 9.00 04/03/10 150 5.00 04/03/10 150 2.00 04/03/10 150 4.00 14/03/10 150 9.00 15/03/10 150 2.00 15/03/10 150 9.00 16/03/10 150 2.00 16/03/10 150 9.00 17/03/10 150 9.00 17/03/10 150 2.00 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total
With date in cell D1 and Emp.ID in cell E1 try the below formula
=SUMPRODUCT((A2:A100=D1)*(B2:B100=E1)*C2:C100) -- Jacob (MVP - Excel) "Yousoft" wrote: Dear All Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID & date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc. Thanks Remarks my data not include any empty raw Working data as below Date Emp #.ID No. of Hours 01/03/10 116 4.00 01/03/10 116 2.00 01/03/10 116 5.00 01/03/10 116 5.00 03/03/10 116 11.00 04/03/10 116 11.00 28/03/10 116 11.00 01/03/10 120 2.00 01/03/10 120 5.00 01/03/10 120 4.00 02/03/10 120 9.00 02/03/10 120 2.00 03/03/10 120 11.00 04/03/10 120 11.00 23/03/10 120 11.00 25/03/10 120 6.00 25/03/10 120 5.00 01/03/10 131 11.00 02/03/10 131 11.00 03/03/10 131 6.00 03/03/10 131 5.00 16/03/10 131 11.00 25/03/10 131 2.00 25/03/10 131 9.00 04/03/10 150 5.00 04/03/10 150 2.00 04/03/10 150 4.00 14/03/10 150 9.00 15/03/10 150 2.00 15/03/10 150 9.00 16/03/10 150 2.00 16/03/10 150 9.00 17/03/10 150 9.00 17/03/10 150 2.00 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total
Thank you for your respond but the result for this formula is wrong,
"Jacob Skaria" wrote: With date in cell D1 and Emp.ID in cell E1 try the below formula =SUMPRODUCT((A2:A100=D1)*(B2:B100=E1)*C2:C100) -- Jacob (MVP - Excel) "Yousoft" wrote: Dear All Please I want formula or VB macro, to sub-total the no. of hours when Emp.ID & date is matching eg. All Emp#.ID equal to 116 with date equal to 1/3/10 etc. Thanks Remarks my data not include any empty raw Working data as below Date Emp #.ID No. of Hours 01/03/10 116 4.00 01/03/10 116 2.00 01/03/10 116 5.00 01/03/10 116 5.00 03/03/10 116 11.00 04/03/10 116 11.00 28/03/10 116 11.00 01/03/10 120 2.00 01/03/10 120 5.00 01/03/10 120 4.00 02/03/10 120 9.00 02/03/10 120 2.00 03/03/10 120 11.00 04/03/10 120 11.00 23/03/10 120 11.00 25/03/10 120 6.00 25/03/10 120 5.00 01/03/10 131 11.00 02/03/10 131 11.00 03/03/10 131 6.00 03/03/10 131 5.00 16/03/10 131 11.00 25/03/10 131 2.00 25/03/10 131 9.00 04/03/10 150 5.00 04/03/10 150 2.00 04/03/10 150 4.00 14/03/10 150 9.00 15/03/10 150 2.00 15/03/10 150 9.00 16/03/10 150 2.00 16/03/10 150 9.00 17/03/10 150 9.00 17/03/10 150 2.00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
| Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
| calculate count on 2006 total, 2005 total, etc... | Excel Worksheet Functions | |||
| Pivots - Auto calc % Sub total is of grand total | Excel Discussion (Misc queries) | |||
| Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
Linear Mode
