Extract and Sum Numerical Values using string matching from single/multi value cell

I'm trying to create a schedule and want to be able to show both (Workers x days) but also an overview of each project and what actions are occuring on what day. I need to be able to sum all allocated time to have knowledge of total time alloted.

Basically, I have "Action"_"hours" with a possibility of "Action_A_hours, Action B_Hours, Action C_Hours, Action D_Hours" and I want to extract any match of "Action X" and sum all hours that match.

I've been working on this overall for over 20 hours and think I need a change of perspective.


From the attachment:
I want to take the data in the Monday column, and where the string matches in the project overview column "2675_Mechanism Drive_Guard Bottom_Fit" I want to sum all numbers that match from the Monday column. The result i'm looking for is highlighted in yellow.

The difficulty for me right now is that some of the cells in the Monday column can be multi tasked, with the comma delimiter as shown in G3.

Try this

D17:
=SUM(IFERROR(--MID($G$2:$G$4,FIND(C17,$G$2:$G$4)+LEN(C17)+1,2),0))
E18
=SUM(IFERROR(--MID($G$2:$G$4,FIND(C18,$G$2:$G$4)+LEN(C18)+1,2),0))

KokiKoki,

Thank you very much. I must've been very close. I had tried something similar to return the workers who matched and I did this through a search and textjoin. The search gave me the array of {1,44,1}
positioning. I figured I could use something similar with mid but couldn't get mid to work in array format and had a little issue finding relevant stuff online.

I guess my error had come when trying to us the string return from search which gave me a 'string array' of {1,44,1}, and I wanted to use those numbers in the mid array formula.

so I had tried:

{MID = (G2:G4, G32, 2)}
where G32 was technically string of 1,44,1.

When I ran that I got #value, and looking at the formula it only showed my G2:G4 as returning G2, which led me down the road of thinking I couldn't use mid in an array or that the string entry didn't work.

Running through it now I see where the error was, I had to use Find(C17, $G$2:$G$4) to get my array of {1,44,1}, but even then the function doesn't work without the '--' prior to the mid.

What does '--Mid()' do that allowed the array to work rather than just Mid()? I see the --Mid in both replies.

Think I found the proper way to reply,

In short, What does '--Mid()' do that allowed the array to work rather than just Mid()? I see the --Mid in both replies.