Finding Individuals on Multiple Sheets, then Calculating Hours Worked

**LTExcel** · 10-08-2012, 02:26 PM

Hello there - I'm trying to find the formula to total hours worked by employees. The time sheets are on multiple worksheets and the Pay Period (Master) worksheet holds the totals for the individual sheets. I have employees listed by Row (first name in column B, last name in column C) and dates worked by Columns.

Problems/Conditions
1. Of the 100 or so employees, only about 40 are assigned to a job/day.
2. Of the 40 or so that are assigned, only about 75% actually show up and collect hours.
3. I have employees with the same last name (column C) or first name (column B) and do not want to combine names in one cell if avoidable.

I almost have the formula to find if the employee appears on the individual time sheets (=COUNTIF('9-22 TC'!C5:C79, C2) "9-22 TC" being the worksheet for the day, C5:C79 being the range of Last Names and C2 being the last name in the Pay Period/Mast sheet. The problem I run into is when the formula finds the same Last Name for multiple people (i.e. I have 3 people with the last name of Evans). So, I need to modify the forumla to find the employee when BOTH the last name and first name appear and count it in the master.

Second problem - if employee "Elias Andrade" signed up to work on Sept-22 but did not show up, he will appear in the search and give me the value of 1 since he was found in worksheet "9-22 TC. Is there a way to find the (1) employee with both first and last name values and then (2) instead of determining the value of the COUNTIF (1), determining the value for the hours worked (in column G of the individual dates worked worksheets). Even though "Elias Andrade" is found on the worksheet 9-22 TC, he did not work and his hours are 0. Employee "Evette Bettale" is also on worksheet 9-22 TC and worked 3.25 hours so instead of the COUNTIF value of 1, her value is 3.25.

Sample is attached. Hope it makes sense!

Thanks!
Lindsay

EDIT/UPDATE: The values/hours entered in the Pay Period/Master worksheet have been entered manually. Trying to find the formula that avoids this work and human error! Thanks!

**uncle_romania** · 10-08-2012, 06:05 PM

You could use a concatenate function to combine the 1st and 2nd names then use the result of this to use a vlookup and retrieve the total hours worked. The summed 1st & 2nd names column can be hidden for neatness. I would also add some fault tolerance to the vlookup to keep it neat.

So add a new column D in your main tab and put this formula in - =CONCATENATE(B5,C5) do the same in your 9-22 TC tab. You can hide these columns then. Then in cell E2 put this formula - =IF(ISERROR(VLOOKUP(D2,'9-22 TC'!D:H,5,FALSE)),0,VLOOKUP(D2,'9-22 TC'!D:H,5,FALSE))

A good article for vlookup without returning errors is here - http://www.msexcelspreadsheet.com/fu...tolerance.aspx