+ Reply to Thread
Results 1 to 19 of 19

Adding hours together

  1. #1
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Adding hours together

    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. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Adding hours together

    This is the Sumif Function.

    Put this is G2 and then fill down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Re: Adding hours together

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

  4. #4
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    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
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    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. #7
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Re: Adding hours together

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

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding hours together

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

    That's the correct formula.

    What's the problem?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    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. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #11
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Re: Adding hours together

    That worked thank you sir!

  12. #12
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    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. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding hours together

    Like this...

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

  14. #14
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Re: Adding hours together

    Where do I put that formula?

  15. #15
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding hours together

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

  16. #16
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    Re: Adding hours together

    That worked great, thank you

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding hours together

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

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

  18. #18
    Registered User
    Join Date
    05-04-2015
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    12

    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. #19
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding hours together

    Can you post an updated sample file to demonstrate that?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding hours to start time - Business hours/holidays/weekends
    By Thunderer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2014, 01:09 PM
  2. [SOLVED] adding hours
    By pannam in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-28-2014, 01:29 AM
  3. [SOLVED] Adding Hours Worked minus lunch break IF over 5 hours
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2013, 11:16 PM
  4. Adding daily run hours to cumulative total hours
    By Rodstew in forum Excel General
    Replies: 8
    Last Post: 08-08-2012, 07:10 PM
  5. adding hours
    By Jonathan78 in forum Excel General
    Replies: 4
    Last Post: 10-02-2009, 04:58 AM

Bookmarks

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