Sum for cell containg text - Sum all row

Hello

Someone at my work trying to create excel sheet that do search for employee assigned task and hours and then sum these product together

Example:

Employees Project1 Project 2 Project 3
Tony 8 Check Sheets (8) Check Sheets (4) Check Sheets (8)
Keith 4 Plans (4) Check Sheets (40) Check Sheets (40)
Chris 4 Plans (4)


Assume that tony has assigned 8 hours to check sheets in project #1 and 4 hour for project # 2 and 8 hr for project # 3

He did this formula :

=IF(ISBLANK(INDIRECT("S19")),0,(MID(INDIRECT("S19"),SEARCH("(",INDIRECT("S19"))+1,SEARCH(")",INDIRECT("S19"))-SEARCH("(",INDIRECT("S19"))-1)+0))

However it only give the result of 4 which is project #1. How can he add the hours in Project 1, 2 & 3 using this formula and give me the total of 20?

is there a macro to do that or better formula?

Thanks

Try…………..

1] Assume your data put in A1:D4 with heading

2] In E2, array formula (Ctrl+Shift+Enter) copied down :

=SUM(IFERROR(-MID(B2:D2,FIND("(",B2:D2),20),0))

Regards
Bosco

Here is the file attachment.

Thanks for you help. It works great. Hopefully this is what they are looking for. Much appreciated!