Sum numbers only in cells with mixed numbers and text

[Moaman]

Hi I am building a shift roster with Day, Afternoon and Night shifts. The Day shift and Afternoon shift may show D10 and A10 for 10 hours and the night shift may show N8 , for an 8 hour shift. I am trying to add up just the hours worked for each engineer but keep getting a VALUE error. I have tried may differnt ways but it's not working.
Any ideas are greatly appricated - Moaman

 

[Fluff]

How about

=SUM(IFERROR(--SUBSTITUTE(D7:AG7,AI7,""),0))

 

[Moaman]

=SUM(IFERROR(--SUBSTITUTE(D7:AG7,AI7,""),0))

Hi Fluff - Thanks for looking at this. I tried the formual but it returned 0. Which , ok ,is better than my VALUE but it needs tweeking abit. Any thoughts.

 

[Moaman]

=SUM(IFERROR(--SUBSTITUTE(D7:AG7,AI7,""),0))

Hi - Just trred again and it worked this time !!!Awsome Thanks

 

[Fluff]

You're welcome & thanks for the feedback.