Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding like job hours
Hi all,
I have a list of several hundred jobs. Each job has a unique JOB ID number which is in column B. For each job, there may be between 1 and 15 rows, depending on the number of employees that worked that job. Each employee would have their own row, and the JOB ID number appears on each row. The number of hours each employee spent on a particular job is in column F. I am trying to create a formula that will look at each JOB ID number starting in cell B2, and add the contents of all cells in column F for that unique JOB ID number. Then the process would start over when the next JOB ID number is found. Here is the layout: A Employee Name B Job ID Number C Job Description D Phase ID E Phase Description F Duration Data starts in row 2 Sample report: A2 B2 C2 D2 E2 F2 Juan ... 2646 ... ABC Co. ... 10 ... Delivery ... 7.0 Alex ... 2646 ... ABC Co. ... 10 ... Delivery ... 4.0 Juan ... 2655 ... XYX Co. ... 10 ... Delivery ... 6.5 So I would like to have this formula add up the hours of 7.0 and 4.0 for job 2646, and do the same for each and every new JOB ID number. I am using Excel 2007 with XP Pro. Anyone have an idea? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding like job hours
On 2 Maj, 00:22, MarkT wrote:
Hi all, I have a list of several hundred jobs. *Each job has a unique JOB ID number which is in column B. *For each job, there may be between 1 and 15 rows, depending on the number of employees that worked that job. *Each employee would have their own row, and the JOB ID number appears on each row. *The number of hours each employee spent on a particular job is in column F. I am trying to create a formula that will look at each JOB ID number starting in cell B2, and add the contents of all cells in column F for that unique JOB ID number. *Then the process would start over when the next JOB ID number is found. * Here is the layout: A Employee Name B Job ID Number C Job Description D Phase ID E Phase Description F Duration Data starts in row 2 Sample report: A2 * * * * B2 * * * *C2 * * * * * *D2 * * * *E2 * * * * *F2 Juan ... 2646 ... ABC Co. ... 10 ... Delivery ... 7.0 Alex ... 2646 ... ABC Co. ... 10 ... Delivery ... 4.0 Juan ... 2655 ... XYX Co. ... 10 ... Delivery ... 6.5 So I would like to have this formula add up the hours of 7.0 and 4.0 for job 2646, and do the same for each and every new JOB ID number. I am using Excel 2007 with XP Pro. Anyone have an idea? Thanks! Hi I assume that Job ID Number is sorted ascending. You didn't say where to place the result, so total job hours will be in column H in last row for the job. Sub Add_JobTime() Dim TargetRange As Range Job = Range("B2").Value Set TargetRange = Range("B2", Range("B2").End(xlDown)) For Each c In TargetRange If Job = c.Value Then jTime = jTime + c.Offset(0, 4).Value Else Job = c.Value c.Offset(-1, 5).Value = jTime jTime = c.Offset(0, 4).Value End If Next Range("B2").End(xlDown).Offset(0, 5).Value = jTime End Sub Regards, Per |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding like job hours
Hi Mark,
Try this in G2 and dragged down to the end of your data. =IF(COUNTIF(B$2:B2,B2)<1,"",SUMIF(B$2:B$100,B2,F$ 2:F$100)) Change the B$100 and F$100 references to whatever your last row is. That should give the total of each Job ID opposite the first occurence of each new ID number it encounters. HTH Martin "MarkT" wrote in message ... Hi all, I have a list of several hundred jobs. Each job has a unique JOB ID number which is in column B. For each job, there may be between 1 and 15 rows, depending on the number of employees that worked that job. Each employee would have their own row, and the JOB ID number appears on each row. The number of hours each employee spent on a particular job is in column F. I am trying to create a formula that will look at each JOB ID number starting in cell B2, and add the contents of all cells in column F for that unique JOB ID number. Then the process would start over when the next JOB ID number is found. Here is the layout: A Employee Name B Job ID Number C Job Description D Phase ID E Phase Description F Duration Data starts in row 2 Sample report: A2 B2 C2 D2 E2 F2 Juan ... 2646 ... ABC Co. ... 10 ... Delivery ... 7.0 Alex ... 2646 ... ABC Co. ... 10 ... Delivery ... 4.0 Juan ... 2655 ... XYX Co. ... 10 ... Delivery ... 6.5 So I would like to have this formula add up the hours of 7.0 and 4.0 for job 2646, and do the same for each and every new JOB ID number. I am using Excel 2007 with XP Pro. Anyone have an idea? Thanks! |
#4
|
|||
|
|||
Quote:
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding like job hours
Hi Martin, that worked perfect! Thank you very much.
"MartinW" wrote: Hi Mark, Try this in G2 and dragged down to the end of your data. =IF(COUNTIF(B$2:B2,B2)<1,"",SUMIF(B$2:B$100,B2,F$ 2:F$100)) Change the B$100 and F$100 references to whatever your last row is. That should give the total of each Job ID opposite the first occurence of each new ID number it encounters. HTH Martin "MarkT" wrote in message ... Hi all, I have a list of several hundred jobs. Each job has a unique JOB ID number which is in column B. For each job, there may be between 1 and 15 rows, depending on the number of employees that worked that job. Each employee would have their own row, and the JOB ID number appears on each row. The number of hours each employee spent on a particular job is in column F. I am trying to create a formula that will look at each JOB ID number starting in cell B2, and add the contents of all cells in column F for that unique JOB ID number. Then the process would start over when the next JOB ID number is found. Here is the layout: A Employee Name B Job ID Number C Job Description D Phase ID E Phase Description F Duration Data starts in row 2 Sample report: A2 B2 C2 D2 E2 F2 Juan ... 2646 ... ABC Co. ... 10 ... Delivery ... 7.0 Alex ... 2646 ... ABC Co. ... 10 ... Delivery ... 4.0 Juan ... 2655 ... XYX Co. ... 10 ... Delivery ... 6.5 So I would like to have this formula add up the hours of 7.0 and 4.0 for job 2646, and do the same for each and every new JOB ID number. I am using Excel 2007 with XP Pro. Anyone have an idea? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding like job hours
You're welcome Mark, thanks for posting back.
Regards Martin "MarkT" wrote in message ... Hi Martin, that worked perfect! Thank you very much. "MartinW" wrote: Hi Mark, Try this in G2 and dragged down to the end of your data. =IF(COUNTIF(B$2:B2,B2)<1,"",SUMIF(B$2:B$100,B2,F$ 2:F$100)) Change the B$100 and F$100 references to whatever your last row is. That should give the total of each Job ID opposite the first occurence of each new ID number it encounters. HTH Martin "MarkT" wrote in message ... Hi all, I have a list of several hundred jobs. Each job has a unique JOB ID number which is in column B. For each job, there may be between 1 and 15 rows, depending on the number of employees that worked that job. Each employee would have their own row, and the JOB ID number appears on each row. The number of hours each employee spent on a particular job is in column F. I am trying to create a formula that will look at each JOB ID number starting in cell B2, and add the contents of all cells in column F for that unique JOB ID number. Then the process would start over when the next JOB ID number is found. Here is the layout: A Employee Name B Job ID Number C Job Description D Phase ID E Phase Description F Duration Data starts in row 2 Sample report: A2 B2 C2 D2 E2 F2 Juan ... 2646 ... ABC Co. ... 10 ... Delivery ... 7.0 Alex ... 2646 ... ABC Co. ... 10 ... Delivery ... 4.0 Juan ... 2655 ... XYX Co. ... 10 ... Delivery ... 6.5 So I would like to have this formula add up the hours of 7.0 and 4.0 for job 2646, and do the same for each and every new JOB ID number. I am using Excel 2007 with XP Pro. Anyone have an idea? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding up hours and minutes | Excel Discussion (Misc queries) | |||
Adding hours and minutes | Excel Worksheet Functions | |||
Adding hours in Excel | Excel Worksheet Functions | |||
Adding Hours worked | Excel Discussion (Misc queries) | |||
adding hours and minutes | New Users to Excel |