SUMPRODUCT - ignoring blanks in the table

[Ronnie12345]

Hi Mr Excel,

I like using SUMPRODUCT but SUMPRODUCT doesn't like blanks in my tables. It returns #VALUE ! when there's a blank.
Is there a quick and easy way get around this, for example using the example below. Apologies for the cumbersome table posting... but cell D6 has a blank which causes the formula to return the VALUE error. Can this blank be ignored?

a b c d e f g h i
[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]

[/TD]
[TD="width: 64"]Smith[/TD]
[TD="width: 64"]Jones[/TD]
[TD="width: 64"]Smith[/TD]
[TD="width: 64"]Jones[/TD]
[TD="width: 64"]Smith[/TD]
[TD="width: 64"]Jones[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Smith[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]64[/TD]
[TD][/TD]
[TD="align: center"]#VALUE ![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]128[/TD]
[TD][/TD]
[TD="colspan: 5"]SUMPRODUCT((B1:G1=I1)*(A2:A17=I2)*(B2:G17))[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]192[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]224[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]288[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]176[/TD]
[TD="align: right"]352[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]384[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]416[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]448[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]480[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


















[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Thank you so much, that works perfectly.

You're welcome.

How does it work? What does 0& actually do?

0& puts a 0 in front of the "" making it "0". When the multiplication of that final term in the formula takes place, Excel knows to interpret that as a zero, which is what we want.
The 0& also puts a zero in front of all the other numbers too but, for example, "028" still gets interpreted as 28 so no problems there.