adding like job hours


M

MarkT

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!
 
Ad

Advertisements

P

Per Jessen

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
 
M

MartinW

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
 
G

GoBow777

MarkT;660269 said:
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!

Mark, this was done on Excel 2003 but it should work for you... I
think.


+-------------------------------------------------------------------+
|Filename: MarkT.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=114|
+-------------------------------------------------------------------+
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top