# 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!  Register To Reply

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))  Register To Reply

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  Register To Reply

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

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

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

Thank you TMS!! this works perfectly   Register To Reply

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

You're welcome. Thanks for the rep.   Register To Reply

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)))  Register To Reply

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

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

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

Awesome!! Thank you Puocam. This works perfectly!  Register To Reply