Ignore zeros in formula

[Fantrasp12]

Hi. Nor an excel expert. But i have a formula that needs to sum and average...but can't use sumproduct because the range is not continuous (due to necessary layout of the assessment that i need to perform. So it goes like this
=((BM21*$H21)+(BT21*$I21)+(CA21*$J21)+(CH21*$K21)+(CO21*$L21)+(CV21*$M21)+(DC21*$N21)+(DJ21*$O21)+(DQ21*$P21))/IF(($H21+$I21+$J21+$K21+$L21+$M21+$N21+$O21+$P21)>0,$H21+$I21+$J21+$K21+$L21+$M21+$N21+$O21+$P21,1)

On the left are weights for topics, on the right are areas that are being assessed. This formula is averaging based on the weights applied (1-5) with the scores (1-5) on the right. The problem is when an area of left blank on the right (because the client doesn't have that area to assess and should be excluded and is left blank) it completely artificially tanks the overall average. I want to modify this formula to exclude anything that is either zero or left blank. I dont really understand arrays, so hopefully this formula can just be modified? If need to use arrays i would need specific instruction on this vs hypothetical. Many thanks

 

[jasonb75]

Do you need

=((BM21*$H21)+(BT21*$I21)+(CA21*$J21)+(CH21*$K21)+(CO21*$L21)+(CV21*$M21)+(DC21*$N21)+(DJ21*$O21)+(DQ21*$P21))/COUNTIF($H21:$P21,">0")

I'm looking at your formula and description, thinking that it should work so I'm obviously missing something. A visual sample of your data showing the problem that you're encountering would help to clarify any uncertainty.