Omit blank cells from a multiple sumproduct

Hi guys,

Getting very frustrated with this I'm trying to avoid my weighted averages being affected by blank cells in the Moisture column.

Under Equals i have a straight sumproduct =sumproduct(moisture,density,thickness/sumproduct(density,thickness))

=SUMPRODUCT(AP676:AP685,$AJ676:$AJ685,$O676:$O685/SUMPRODUCT($AJ676:$AJ685,$O676:$O685))

Thickness Density Moisture
0.74 1.39 36.5
0.20 2.47
0.20 2.76
0.31 1.72 22.1
0.38 2.26
0.44 1.42 31.0
0.28 2.64
0.41 1.42 29.9
0.20 2.46
0.20 2.17

Obviously the sumproduct moisture values weighted against both density and thickness keep coming out far to low as the blanks are being included as a zero i presume.

Any help would be great many thanks

Hi DILIPandey,

Thanks for your response. I have uploaded a file, the only way i can see to counteract the blank cells is to delete lines which are not relevant to avoid an anomalous weighted average.

This is only a snippet of my data and if i can avoid filtering and segregating out relevant data for the sake of a weighted average i would be pretty happy.

I'm a bit of a novice at all this... appreciate the help.

Many thanks

That works perfectly. Thanks so much for your help, much appreciated.