SUMIF - match multiple names per cell?

[Ajw43022]

Not sure how to do this.

I have two columns. The first has 1-4 employee names in each cell, separated by commas. The second has an integer (hours).

I need a formula that calculates the cumulative hours worked by each employee.

This seems like SUMIF territory, but multiple employees to a cell presents a twist I don't know how to handle.

Any thoughts on doing this in Excel?
(What about in google spreadsheets, if possible?)

Thanks tons for any help.

 

[jasonb75]

With names in A1 and hours in B1, try

=(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)*B1

Does that do what you're looking for?

edit:-

looking at barry's reply, I now realise that I mis-read what you wanted to do.

 

[barry houdini]

If you want to calculate the hours for a specific employee try SUMIF with a wildcard, e.g.

=SUMIF(A:A,"*"&D1&"*",B:B)

where D1 contaisn a specific employee name

 

[Ajw43022]

worked like a charm, thank you!