+ Reply to Thread
Results 1 to 8 of 8

Help with a formula/Function for totaling hours for a job

  1. #1
    Registered User
    Join Date
    07-02-2020
    Location
    Niagara Falls
    MS-Off Ver
    2016
    Posts
    4

    Help with a formula/Function for totaling hours for a job

    Hello

    I've been wracking my small brain on trying to figure this out for my wife. She has one workbook with 7 sheets. the rows of each sheet are the weeks of the year and every two columns is a job, the first of the two being hours the second being hrs times rate of pay. Now each sheet is the same as in the 8th column will all be the same job. I'm trying to figure out how to scan a specific job hours column in every sheet and display results of any row that is not null/zero. This display on another sheet. That way when her boss asks how much labour on job x, she could this formula to figure it out instead of going through each sheet and totalling numbers etc etc.

    Any help would be greatly appreciated as my brain is not wrapping around what functions to use and in what order.

    Joel

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    696

    Re: Help with a formula/Function for totaling hours for a job

    Hi
    Please read the yellow banner at the top of the page explaining how to upload a sample file.

  3. #3
    Registered User
    Join Date
    07-02-2020
    Location
    Niagara Falls
    MS-Off Ver
    2016
    Posts
    4

    Re: Help with a formula/Function for totaling hours for a job

    Oops, sorry

    Here is an example
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    696

    Re: Help with a formula/Function for totaling hours for a job

    So seet "Empoyee 7" is your sum up results?
    How did you get to 15.5 hours on pay period Dec 29-Jan 3?
    your job is "19-1020"?

  5. #5
    Registered User
    Join Date
    07-02-2020
    Location
    Niagara Falls
    MS-Off Ver
    2016
    Posts
    4

    Re: Help with a formula/Function for totaling hours for a job

    Hello

    No Employee 7 is a new employee who they haven't made a full sheet for yet. He can be deleted probably as his would mess things up lol
    Last edited by dmferd; 07-02-2020 at 05:31 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,152

    Re: Help with a formula/Function for totaling hours for a job

    I introduced a summary worksheet: "main" a portion of which is shown below.

    dmferd.png

    The formula in cell main!B2 copied across and down is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that there are a few #N/A errors. These are due to "job" columns missing from certain employees, for example 19-1026 is missing from the Employee2 worksheet.

    There's also a couple of #REF errors - for example you have #ref! errors in Employee4!AM30:AM60 that will lead to erroneous results

    Either the data can be cleaned up or possibly I can adjust the formulas to tolerate these errors. I'll leave work on this until you confirm that this solution is along the lines you were looking for.

    The above is implemented in the attached workbook.

    Hope this helps
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  7. #7
    Registered User
    Join Date
    07-02-2020
    Location
    Niagara Falls
    MS-Off Ver
    2016
    Posts
    4

    Re: Help with a formula/Function for totaling hours for a job

    This looks awesome, thank you, I will check it out and fix things over the weekend and let you know

    Joel

  8. #8
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,152

    Re: Help with a formula/Function for totaling hours for a job

    Don't work too hard over the holiday weekend
    Thanks for the feedback and reputation points.

+ 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. Need Help!! What formula would you use for totaling hours across various projects
    By thestatechamp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2017, 05:47 PM
  2. [SOLVED] Totaling hours in hh:mm format
    By lapot in forum Excel General
    Replies: 17
    Last Post: 12-16-2015, 01:09 PM
  3. Totaling hours
    By OnTheCouch in forum Excel General
    Replies: 2
    Last Post: 08-16-2010, 01:02 PM
  4. Problem with totaling Hours between Date
    By Raiderman in forum Excel General
    Replies: 1
    Last Post: 12-23-2008, 12:37 AM
  5. [SOLVED] Totaling some hours but not others HELP!
    By vanilla_bean_orange via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-16-2006, 06:10 AM
  6. Totaling Hours in Standard Format
    By Coco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  7. Totaling Hours in Standard Format
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 06:05 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