Find Avg of weighted values, dependent on multiple criteria, with weightings in 3rd table

Hi Guys,
First post, hope I'm doing this right!

I have Table1 which contains the values I need to Average into a column in Table2. The selection of rows in Table1 for the Avg is based on multiple criteria matching every row of Table2.

My problem is with solving the requirement for me to use weighting in the Average calculation. Table1 contains two columns, which combined can be used to look up matching values in a third table, Table3. Table3 provides the weighting value that I want to use in Table2's function that gets the Average from Table1.

Normally I would have accomplished this using Sumproduct, so naturally I tried to add an Index Match Array function to get the weighting value and multiple against the avg column, but because I have multiple criteria I needed to use an array function, and gave an error, which I think is because Sumproduct can't be used that way.

Here's my function in [Table2]:
=SUMPRODUCT(
Table1[Cost] *
([@Provider]= Table1[Provider]) *
([@Term]= Table1[Term])
) /
COUNTIFS(
Table1[Provider], [@Provider],
Table1[Term], [@Term])

Here's the formula that does not work where I've attempted to get the weight:
=SUMPRODUCT(
(Table1[Cost] * INDEX(Table3[Weight], MATCH(1, (Table1[Provider] = Table3[Provider]) * ( Table1[Category] = Table3[Category]), 0))) *
([@Provider] = Table1[Provider]) *
([@Term] = Table1[Term])
) /
COUNTIFS(
Table1[Provider], [@Provider],
Table1[Term], [@Term])

This also doesn't work:
=SUMPRODUCT(
Table1[Cost] *
([@Provider]= Table1[Provider]) *
([@Term]= Table1[Term]) ,
(Table1[Category] = Table3[Category]) *
([Provider] = Table3[Provider])
) /
COUNTIFS(
Table1[Provider], [@Provider],
Table1[Term], [@Term])


Spreadsheet attached with example.

Thank you!

[removed post]

Hello butterpouch and Welcome to Excel forum.
This proposed solution employs a helper column, which may be hidden for aesthetic purposes.
The array entered formula that populates the helper column (H) is:

Formula:

Please Login or Register  to view this content.

If I understand what you want correctly then the formula that should populate the 'Average Weighted Cost' column on table 2 is:

Formula:

Please Login or Register  to view this content.

If I have misunderstood then please post the expected value for at least cell L4.
Let us know if you have any questions.

Due to mess-up with range issue, I converted table format into ranges.

Try

K3

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Check the attached file.

Thank you JeteMc,
Not exactly what I'm looking for, but close. @Category doesn't exist in Table2, to get it I have to lookup Table1. That lookup from Table2 to Table1 may return many results, and I then want to multiply each one against a weight value by performing yet another lookup from Table1 to Table3, however in this case there should only be one result. In your solution I believe it relies on adding a column to Table1 matching with a single record in Table3 to get the weighting, but I can't actually make changes to Table1 in this scenario.

Table1 comes from a data connection, and I'm not able to modify it. Table2 and Table3 however I can modify.

Finally, for the result in L4 I would expect the same result you have provided, so that is correct. The only issue is I can't modify Table1

Originally Posted by JeteMc

Hello butterpouch and Welcome to Excel forum.
This proposed solution employs a helper column, which may be hidden for aesthetic purposes.
The array entered formula that populates the helper column (H) is:

Formula:

Please Login or Register  to view this content.

If I understand what you want correctly then the formula that should populate the 'Average Weighted Cost' column on table 2 is:

Formula:

Please Login or Register  to view this content.

If I have misunderstood then please post the expected value for at least cell L4.
Let us know if you have any questions.

Hi Shukla,

Thank you very much! I removed the countif division and the result is perfect. I should have clarified that I'm looking for a result of 18.9561 in K4 and I'm sure you would have figured that out:

{=SUMPRODUCT(($D$3:$D$17=I4)*($E$3:$E$17=J4)*IF($D$3:$D$17=TRANSPOSE($M$3:$M$7),IF($G$3:$G$17=TRANSPOSE($N$3:$N$7),TRANSPOSE($O$3:$O$7)*$F$3:$F$17,0),0))}

Originally Posted by shukla.ankur281190

Due to mess-up with range issue, I converted table format into ranges.

Try

K3

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Check the attached file.

My final solution came from the response provided by shula.ankur281190 (Thank you!)

Please Login or Register  to view this content.

Final solution attached