+ Reply to Thread
Results 1 to 8 of 8

Issues with sum of multiple formulas

  1. 03-29-2019, 05:09 PM #1
    crimsonmarshal
    crimsonmarshal is offline
    Registered User
    Join Date
    03-29-2019
    Location
    jacksonville florida
    MS-Off Ver
    2017
    Posts
    5

    Issues with sum of multiple formulas

    Hello. I'm trying to track hours during the week. each day has a formula to total the hours that day and then I want to total each day into a total hours worked for the week, but the total is not summing up how I had expected. Find screen shot included below. You'll see the formula I use for each day in the text box above. The column labeled WEEK is where I'm adding up all the daily totals which should be 32:53 but It's producing a strange number

    Excel snip.JPG

    I'm sure I'm doing something very silly, but I'm at a loss. Any help would be greatly appreciated
    Reply With QuoteRegister To Reply
  2. 03-29-2019, 05:25 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Issues with sum of multiple formulas

    Use Custom format
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 03-29-2019, 05:30 PM #3
    crimsonmarshal
    crimsonmarshal is offline
    Registered User
    Join Date
    03-29-2019
    Location
    jacksonville florida
    MS-Off Ver
    2017
    Posts
    5

    Re: Issues with sum of multiple formulas

    Interesting. That worked. Thank you. I'd be curious to know why it has to be formatted to include seconds in order to sum up hours and minutes
    Reply With QuoteRegister To Reply
  4. 03-29-2019, 05:44 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Issues with sum of multiple formulas

    You're welcome.


    It's not formatted to show seconds. The square brackets force the format to show hours greater than 24.




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  5. 03-29-2019, 06:07 PM #5
    crimsonmarshal
    crimsonmarshal is offline
    Registered User
    Join Date
    03-29-2019
    Location
    jacksonville florida
    MS-Off Ver
    2017
    Posts
    5

    Re: Issues with sum of multiple formulas

    A minor follow up however. I'm now totaling a column which has zero as a value. A screen shot it included. It seems to be adding the value of G5 and G6 instead of subtracting it. Also, in the other formulas I have set them up where the larger number proceeds the smaller but this may not always be the case, so is there a way I can format the formulas to just subtract positive values without producing a negative error?
    Excel snip.JPG
    Reply With QuoteRegister To Reply
  6. 03-29-2019, 06:38 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Issues with sum of multiple formulas

    Your formula is not consistent with that in column C.
    Reply With QuoteRegister To Reply
  7. 03-29-2019, 06:42 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Issues with sum of multiple formulas

    You have to decide what is appropriate. Test which is greater before subtracting. Or add 1 to the end time if it is earlier than the start time ... that is, it's the next day. Various ways around these things but only you know what your rules are.
    Reply With QuoteRegister To Reply
  8. 03-29-2019, 06:45 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,745

    Re: Issues with sum of multiple formulas

    I agree with Trevor. It appears you are trying to compensate for the larger value in G4. Please try in G8
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    the +(G4>G7) part adds a day to G7 if it is smaller than G4 and zero if it is not.
    Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Has anyone else had issues with formulas not calculating in Excel 2016?
    By Grimace in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2017, 08:03 PM
  2. [SOLVED] Formulas hidden with If cause =Product issues
    By bilalo94 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 04-02-2017, 12:05 PM
  3. 2 New Issues Today with Formulas
    By runelady in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2016, 10:09 AM
  4. [SOLVED] conditional formating issues with formulas
    By slaters in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-28-2015, 01:48 PM
  5. Issues with alpha order and complex formulas
    By rifeca88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2015, 11:15 AM
  6. Cross referencing formulas issues
    By mdev in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2010, 02:26 AM
  7. Some Subtle Issues with Some Formulas
    By Niester Rabbit in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2007, 09:52 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1