# Need help with a sum formula that omits cells

1. ## 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!

2. ## 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. ## 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. ## Re: Need help with a sum formula that omits cells

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

5. ## Re: Need help with a sum formula that omits cells

Thank you TMS!! this works perfectly

6. ## Re: Need help with a sum formula that omits cells

You're welcome. Thanks for the rep.

7. ## 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. ## Re: Need help with a sum formula that omits cells

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

9. ## Re: Need help with a sum formula that omits cells

Awesome!! Thank you Puocam. This works perfectly!

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