Ignoring NA or empty cells when adding, multiplying and dividing all in one formula

Dear Excelers,

I hope you are well.

I have the following formula that uses coefficients to calculate a specific score:

(Value1*coefficient1 + Value2*coefficient2 + Value3*coefficient3) / (coefficient1+coefficient2+coefficient3)

I need a formula that not only ignores if any ValueX is #N/A but also if the ValueX is #N/A (or blank cell), its corresponding coefficient should be also #N/A when the coefficients are being added

For example if Value2 is #N/A the formula should calculate as follows

(Value1*coefficient1 + Value3*coefficient3) / (coefficient1+coefficient3)

Is it possible?

Thanks for your help

try ...

=SUM(IF(ISERROR(A1:C1),0,A1:C1*A2:C2))/SUM(IF(ISERROR(A1:C1),0,A2:C2))

Enter with Ctrl+Shift+Enter

A1:C1 values

A2:C2 coefficients