SUMPRODUCT - ignoring blanks in the table

Ronnie12345

New Member
Joined
Mar 24, 2017
Messages
18
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


SmithJonesSmithJonesSmithJonesSmith
Apples12481632Apples
Pears248163264#VALUE !
Bananas3612244896
Oranges48163264128SUMPRODUCT((B1:G1=I1)*(A2:A17=I2)*(B2:G17))
Apples510 4080160
Pears612244896192
Bananas7142856112224
Oranges8163264128256
Apples9183672144288
Pears10204080160320
Bananas11224488176352
Oranges12244896192384
Apples132652104208416
Pears142856112224448
Bananas153060120240480
Oranges163264128256512

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



















<tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGHI
1SmithJonesSmithJonesSmithJonesSmith
2Apples12481632Apples
3Pears248163264568
4Bananas3612244896
5Oranges48163264128
6Apples5104080160
7Pears612244896192
8Bananas7142856112224
9Oranges8163264128256
10Apples9183672144288
11Pears10204080160320
12Bananas11224488176352
13Oranges12244896192384
14Apples132652104208416
15Pears142856112224448
16Bananas153060120240480
17Oranges163264128256512
SUMPRODUCT with blank



Are you able to upload your problem file to a public file-share site (eg Dropbox) and provide a link here?
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top