Equation for a summary table of hours worked

**ValerieD** · 06-11-2010, 03:38 PM

Hi!

I own a small business where we get work orders of work to do and we have to predict the hours that an employee will be working on a certain work order, based on three roles. The hours are spread up on 4 periods.

What I want to do is create an Hour summary chart to inicate how many hours per period an employee has worked (independant of the role since an employee can be assigned more than role) on a certain work order.

I have included an example of what I currently have, which doesnt work. I have some programming knowledge so I would be comfortable with these solutions as well.

In the example, the first table is to keep track of work orders, and who has what role. The second table is the report I would like to produce. As it stands, I use SUM (IF (role1), if(role2),if(role3)) to add all hours within a period, but to add a new work order, I have to add 4 lines, copy my equation to the 4th line added, and delete the first 3 so that the fields keep corresponding.

If this isnt clear, ask questions and I will answer as best as I can. Thank you!

**ChemistB** · 06-11-2010, 04:39 PM

In C24, dragged right;
=SUMIF($E$7:$E$9,$F$20,G$7:G$9)
In C25
=SUMIF($E$11:$E$13,$F$20,G$11:G$13)
In C26
=SUMIF($E$15:$E$17,$F$20,G$15:G$17)
Does that work for you?

**ValerieD** · 06-13-2010, 10:19 AM

Thanks for that idea. However, the equation itself is not the problem, its the fact that I have to add 4 lines in order to copy my equation to the 4th line, and then delete the other3 for each new work order if I want my equation to correspond to the proper cells.

Any ideas? Could this be programed with macros?

**luv2glyd** · 06-13-2010, 12:14 PM

You (to be continued in a few minutes)

**luv2glyd** · 06-13-2010, 12:33 PM

You can use INDIRECT function with a combination of other imputs, but the simplest way is probably with a macro. I'd move your totals row (in cells B27:F27) to the top of the table (say to B23:F23) and have it sum 10 to 100 rows or however many you need.

Then delete all the $ signs from row 24 except for the F20 link. Delete rows 25 and 26. Start recording a macro.

- select row 24 and copy it
- paste it 4 rows down
- cut it and paste it to row 25
- stop macro recording

this achieves recording work order 2 only, though you need to modify the macro, so that it knows how many rows of workorders you have and have it step one row down each time you record, otherwise you'll be always recording to row 25.

Attached is an example of how to do this stepping process. See if you can use the code and modify it to fit your spreadsheet.