Lookup multiple values using a single word, retrieve values, sum, then divide by 20

Hello all

As you may (or may not!) guess from the title above, I am attempting to create a rather complex (at least I think it is) formula which does the following:

I have worksheet 'A' which contains three columns: Column A - a list of names (eg. John Smith); Column B - a role-title for each name (eg. Plumber); and Column C - a list of days worked next to each name (eg. 20).

In worksheet 'B', I want to run a formula which looks up a given value (eg. Plumber) and searches for all instances of this value in Worksheet 'A' (so, all of the Plumbers), and then totals all of the days worked for the plumbers. When this is done, I want to divide the total figure by 20, and display this final result in the cell.

The key thing I'm trying to do here is be able to search for a value, and retrieve that value even if it is nestled in a larger value. So if I search for 'plumber', I want to be able to retrieve the days worked for a resrouce who is listed as 'plumber', 'senior plumber', 'junior plumber'.

As always, your help will be deeply appreciates.

Many thanks in advance

Shaun G

Suppose you have Plumber in A2 of Sheet B - then you can have this formula in B2 of that sheet:

=SUMIF('A'!B:B,"*"&A2&"*",'A'!C:C) / 20

Hope this helps.

Pete