Conditional weighted average ignoring #N/A values

Hello,

First post, so please direct me elsewhere if need be. I want to calculate the weighted average of the one column (i.e., Accuracy in the attachment) with the another column (i.e., Sample Size) column functioning as the weight. Normally this equation would work:

=sumproduct(B2:B11,C2:C11)/SUM(C2:C11)

BUT I'd also like to only calculate the weighted average based on whether the Skilled column is "Yes" or "No". Normally I could get by with this equation:

=sumproduct(B2:B11,C2:C11,(D2:D11="Yes")+0)/sumproduct(C2:C11,(D2:D11="Yes")+0)

Or an array formula:

{=sum((B2:B11)*(C2:C11)*(D2:D11="Yes"))/sum(C2:C11*(D2:D11="Yes"))}

However, neither of those work in the presence of the #N/A values, as found in the attachment or the image. Unfortunately, that data is to be dynamically populated and I can't guarantee that there won't be those #N/A values present when this worksheet is used. I've tried adding additional conditional parameters to account for it, but I haven't been able to get it to work. I can't get it to work even ignoring the "Skilled" conditional altogether.

Does anyone have any thoughts? I'm afraid I have nobody at work to turn to.

Thanks very much for the help.

Greenshot_2012-02-21_22-16-12.png

Thanks dilipandey! That did the trick right away. I actually didn't know you could combine mathematical calculations with array formulas, so you've taught me a few things today! And I added to your rep.