Weighted average that conditionally sums based on name and ignores #N/A

[cmshawks2]

In this sheet, I have already set up column A to autofill each unique name from a list. For each of these names I set up an array to pick values from a data set based on the 2 possible names, "L09 Cracks" and "L10 Cracks" in column b and the headers in C through K. I need to weight average each column C through K for L09 Cracks and L10 Cracks divided by column M "chip weight". I tried IF(AB:AB="L09 Cracks",SUMPRODUCT(AC:AC,AN:AN)/SUM(AM:AM). I cannot get any kind of formula to work, either from N/A messing it up or it not reading the criteria correctly.pqm-example.xlsx

[oeldere]

With sumproduct.

See the attached file.

[cmshawks2]

So the columns in A and B are not consistent, right now there are three L09 Cracks and 4 L10 Cracks, but those change consistently, it could be 1 and 1 or 20 and 4, so that is why I cannot do a simple Sumproduct just on the rows they are on. Also it ignores the N/A problem

[oeldere]

Replace the N/a with 0.

Did you look at my answer in the file?

It works with differant ranges.