total
in [General]
|
From: Yousoft on 13 Apr 2010 06:12 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
From: Jacob Skaria on 13 Apr 2010 06:18 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 >
From: Eduardo on 13 Apr 2010 08:19 Hi, I assume your data is in column A B and C and you enter in Cell D1 the date and in E1 the emp #, ;then in F1 you want the sum =Sumproduct(--(D1=$A$1:$A$1000),--(E1=$B$1:$B$1000),$C$1:$C$1000) change range to fit your needs, number of rows has to be identical in each part of the formula "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 >
|
Pages: 1 Prev: Excel 2003 to Excel 2007 graphs Next: Change Track Changes Colour |