How to solve SUM with several requirements

Hi everyone,

I'm stuck with following issue:

In Cells B9 to B15, I have a list of "Training functions". On the second worksheet, I have a reference table with the training requirements. So for example if someone need the function "ELW 2 Drone", he needs to attend 6 lessons "ELW 2" and 6 lessons "Drone". Someone else requiring "ELW Drone" (without the "2") only needs to attend 5 drone lessons according to the table.

Now I'd like to have an overview in Cells A2 to A4 on the total amount of lessons needed for the specific requirement.

Let's say I have 3 guys aspiring for "ELW 2 Drone" and 1 guy for "ELW Drone", I should have the total amount of lessons in Cell B2 for the Drone (e.g. 3x 6 lessons + 1x 5 lessons = 23) and the total amount of "ELW 2" lessons in Cell B4 (3x 6 lessons = 18).

I startet trying with =IF(COUNTIF(B$9:B$15;"*Drone*");COUNTIF(B$9:B$15;"*Drone*");"") but then got stuck referencing to the amount of lessons in the second worksheet's reference table. In addition, this way I need to manually update the search terms.

Every help is highly appreciated!

Is the range B3:D5 expected results? and those formulas did not work?
Try to manual input the expected outcome, not formulas that did not work.

Please try in B3 and copy to the right and down:

Formula:

Please Login or Register  to view this content.

Are you open for an UDF solution (User define formula, using VBA)?