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

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

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[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.