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]

That formula should work fine with a blank in D6. I suspect something is actually in there. In a vacant cell, what does this formula return?

=CODE(D6)

 

[Joe4]

I don't think the problem is with the blanks, but rather with the size of your ranges.
In SUMPRODUCT, the size of your ranges that you are checking must be the same. Yours is not.
B1:G1 is not the same size as A2:A17 or B2:G17.

 

[Ronnie12345]

Hi Peter, it returns '#VALUE!'

 

[Ronnie12345]

I don't think the problem is with the blanks, but rather with the size of your ranges.
In SUMPRODUCT, the size of your ranges that you are checking must be the same. Yours is not.
B1:G1 is not the same size as A2:A17 or B2:G17.

Hi Joe,
I think the ranges are fine because when I type a number in the offending cell I get a result

 

[Peter_SSs]

Hi Peter, it returns '#VALUE!'

OK, well that does indicate blank so there must be something else a bit unusual somewhere in your data. Here is your original formula working for me on your sample data.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Smith	Jones	Smith	Jones	Smith	Jones		Smith
2	Apples	1	2	4	8	16	32		Apples
3	Pears	2	4	8	16	32	64		568
4	Bananas	3	6	12	24	48	96
5	Oranges	4	8	16	32	64	128
6	Apples	5	10		40	80	160
7	Pears	6	12	24	48	96	192
8	Bananas	7	14	28	56	112	224
9	Oranges	8	16	32	64	128	256
10	Apples	9	18	36	72	144	288
11	Pears	10	20	40	80	160	320
12	Bananas	11	22	44	88	176	352
13	Oranges	12	24	48	96	192	384
14	Apples	13	26	52	104	208	416
15	Pears	14	28	56	112	224	448
16	Bananas	15	30	60	120	240	480
17	Oranges	16	32	64	128	256	512
SUMPRODUCT with blank

Are you able to upload your problem file to a public file-share site (eg Dropbox) and provide a link here?

 

[Peter_SSs]

Another test. What does =COUNT(B2:G17) return with that sample data?

 

[Ronnie12345]

Hi Peter,

Thanks for your perseverance!
It's possible to replicate my mess by typing ="" into the offending cell.
Counting produces 15 not 16.

I shall try to find a way of uploading the spreadsheet.

Best wishes,

Matt

 

[Peter_SSs]

Counting produces 15 not 16.

Don't really understand that. Count should be 95 for that data shouldn't it?

It's possible to replicate my mess by typing ="" into the offending cell.

Hmm, I should have thought of formulas.
There should be no need to upload at this stage. Try this formula instead.

=SUMPRODUCT((B1:G1=I1)*(A2:A17=I2)*(0&B2:G17))

 

[Ronnie12345]

Thank you so much, that works perfectly.
How does it work? What does 0& actually do?