weighted average with sumproduct and offset

Question

I would like to do the following: Calculate a weighted average based on the scenario shown in the sample screenshot. The question I would like to anser is: What is the total amount of sales generated by employee A and employee B, respectively, in a given period (here: in 2021).

Screenshot:

My Idea was to use a sumproduct formula which selects first all items with reference to employee A and, secondly, all sales of a given product. I use the ISNUMBER(FIND()) construct as wildcard (e.g. with *) does not work with SUMPRODUCT() as far as I know. However, this formula does not work properly. Any suggestions?

=SUMPRODUCT(--(Table1[item]=$J46);Table1[2021];--(ISNUMBER(FIND("Sale";TabLe1[item])));Table1[2021])

Answer 1

Office 365 required: =SUMPRODUCT(FILTER(Table1[2021],LEFT(Table1[item],4)="sale"),FILTER(Table1[2021],Table1[item]=$J46))

For older Excel version you could use:

=SUMPRODUCT((Table1[2021])*--(Table1[item]=J49),. 
            (OFFSET(Table1[2021],1-MATCH(J49,Table1[item],0),,))*
            --(LEFT(OFFSET(Table1[item],1-MATCH(J49,Table1[item],0),,),4)="sale"))

This is provided that each employee is offset the same way for each sale product.