+ Reply to Thread
Results 1 to 9 of 9

Need help with a sum formula that omits cells

  1. #1
    Registered User
    Join Date
    12-31-2017
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    5

    Need help with a sum formula that omits cells

    Hi,

    I have a formula that adds up the total amount of hours worked for a scheduled work week that works but i cant seem to figure out how to omit cells from the formula that have text in them instead of a time. Since the schedule is always changing i would like to be able to write text into any of the boxes instead of a work time if need be without having to change the formula each time. As of right now when i enter in a word to one of the cells i get #VALUE!

    Here is the formula i have so far =24*(SUM((C12-B12)+(E12-D12)+(G12-F12)+(I12-H12)+(K12-J12)+(M12-L12)+(O12-N12)))

    So C12 is the out time on Monday and b12 is the in time then so on for Tuesday through Sunday.

    I attached a photo if that helps.

    Thank you!
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,402

    Re: Need help with a sum formula that omits cells

    Try this:

    =24*(SUM(C12,E12,G12,I12,K12,M12,O12)-SUM(B12,D12,F12,H12,J12,L12,N12))

  3. #3
    Registered User
    Join Date
    12-31-2017
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need help with a sum formula that omits cells

    That didn't work. It left me with an answer of 12:30 AM

  4. #4
    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
    36,273

    Re: Need help with a sum formula that omits cells

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    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


  5. #5
    Registered User
    Join Date
    12-31-2017
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need help with a sum formula that omits cells

    Thank you TMS!! this works perfectly

  6. #6
    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
    36,273

    Re: Need help with a sum formula that omits cells

    You're welcome. Thanks for the rep.

  7. #7
    Registered User
    Join Date
    12-31-2017
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need help with a sum formula that omits cells

    Ok so here is something i noticed.. haha. I realized that if the shift goes through midnight it ends up coming up as a negative number.

    Is there a way to add in an additional formula to the statement to each subtraction piece that checks if the answer for those two cells is negative and if so it adds 24 to it?

    Here is the Statement TMS came up with.

    =24*((IFERROR((C17-B17),0)+IFERROR((E17-D17),0)+IFERROR((G17-F17),0)+IFERROR((I17-H17),0)+IFERROR((K17-J17),0)+IFERROR((M17-L17),0)+IFERROR((O17-N17),0)))

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,402

    Re: Need help with a sum formula that omits cells

    Change IFERROR((C17-B17),0) to =IFERROR(MOD(C17-B17,1),0)

  9. #9
    Registered User
    Join Date
    12-31-2017
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Need help with a sum formula that omits cells

    Awesome!! Thank you Puocam. This works perfectly!

+ 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] Formula to return N for cells with 0 and empty cells, and Y for cells with data
    By stpeter in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-13-2016, 05:20 PM
  2. Formula checks multiple cells for words and outputs answer depending on cells
    By keez1993 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2015, 09:18 AM
  3. Average formula which omits 0 in calc
    By Storm85 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2013, 02:39 AM
  4. [SOLVED] Determine End Date that omits weekends and holidays
    By mrh_consulting in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 11:44 PM
  5. Scatter Plot that omits blank cells?
    By dsklein85 in forum Excel General
    Replies: 1
    Last Post: 05-03-2012, 11:32 AM
  6. String Function - Omits Zero
    By nivassrii in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2011, 02:43 AM
  7. Formula: Drop Down List -> Choose Option Finds Cells & Replace Cells with Cells
    By g00glethis1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2010, 01:10 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1