Hello, I am new to doing complicated excel worksheets.

However there is something I have to figure out.

I have a time sheet - within the time sheet I have Employee # (A2) and Employee name (B2). I also have there start time (E2) and end time (F2), I have excel calculating the hours the employee worked (G1)

Down in the spreadsheet I have Employee # (A21) and Employee Name (B21) and total hours (C21)

There can be many rows that have the same employee # and name with more hours.

I need to write an if statement that says if A21 equals any number in column A I need those hours to be calculated together c21

Any help would be appreciated

2. ## Re: Adding hours together

This is the Sumif Function.

Put this is G2 and then fill down

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Adding hours together

I need the calculated hours to be in C21 is that where I put the statement?

4. ## Re: Adding hours together

I am going to load my spreadsheet because I don't think I was able to explain myself as well as I needed to

The cell in purple is where I want the hours to be calculated

The cells in in blue is where I want the numbers to match

The cells in yellow is what I want added to the cell in purple if the cell in blue matches that employee number

I hope all this makes sense

Thanks

5. ## Re: Adding hours together

Post #2 was correct. See below where I added some dummy data...
 A B C D E F G 1 Emp # Employee Name Code Reason Start Time End Time Hours 2 aa 5:00 PM 6:00 PM 1:00 3 bb 3:00 PM 6:00 PM 3:00 4 cc 1:00 PM 5:00 PM 4:00 5 aa 11:00 AM 3:00 PM 4:00 6 cc 9:00 AM 12:00 PM 3:00 7 bb 7:00 AM 8:00 AM 1:00 8 #VALUE! 9 #VALUE! 10 Total Hours 12:00 11 12 13 Epm # Employee Name Hours Time Worked 14 aa 5:00 15 bb 4:00 16 cc 7:00

B14=SUMIF(\$A\$2:\$A\$9,A14,\$G\$2:\$G\$9)
copied down

This needs to be fomatted as time, same as you have in G2

6. ## Re: Adding hours together

Can someone please tell me what I did wrong. I copied and pasted the formula and changed the numbers here I what I have

=SUMIF(\$A\$2:\$A\$16,A21,\$G\$2:\$G\$16)

Thank You

7. ## Re: Adding hours together

I still can't figure this out, will someone help me some more please?

8. ## Re: Adding hours together

=SUMIF(\$A\$2:\$A\$16,A21,\$G\$2:\$G\$16)

That's the correct formula.

What's the problem?

9. ## Re: Adding hours together

The problem is that the formula is showing when I go out of the cell. The numbers are not showing like they should just the formula that I posted. The formula is showing up in the window but in the cell I see ######

10. ## Re: Adding hours together

Worked OK for me.

Try this...

Enter the formula in the cell
Format the cell as [h]:mm
Press function key F2
Press Enter

11. ## Re: Adding hours together

That worked thank you sir!

12. ## Re: Adding hours together

I might be pressing my luck but is there a way to have excel enter the name that goes with that number down in the bottom part automatically

13. ## Re: Adding hours together

Like this...

=VLOOKUP(A21,A\$2:B\$16,2,0)

14. ## Re: Adding hours together

Where do I put that formula?

15. ## Re: Adding hours together

Looks like you want it in B21 (based on your posted sample file).

16. ## Re: Adding hours together

That worked great, thank you

17. ## Re: Adding hours together

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

18. ## Re: Adding hours together

I have one more question. I am adding the hours together at the bottom. Unless there is a number in every cell, excel will not add the numbers for me. Is there a
way to get around this.

Thank you

19. ## Re: Adding hours together

Can you post an updated sample file to demonstrate that?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1