SUMPRODUCT error when blank cell

jcooooper

Board Regular
Hi all,

Hopefully an easy one.

=SUMPRODUCT((\$C:\$C="Equity")*(\$E:\$E)*(B:\$B))

Essentially want to multiply and sum Column E and B together if C = "Equity"

Columns E and B contain empty cells, which appears to be the root of the problem.

Any ideas?

Thanks,
John

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott Huish

MrExcel MVP
If columns B & E are actually empty, that would equate to 0 in your formula. So what is your problem exactly?

jcooooper

Board Regular
Sorry - columns B and E contain a mix of values and empty cells

jcooooper

Board Regular
If it makes any difference, column B always has a value, and column E has a mix of values and blanks

Scott Huish

MrExcel MVP
Ok, but blanks still evaluate to 0. Do you mean they're not actually blank, as in empty string "" or space " " or what?

As you can see, it's not failing as you have described the problem:

Excel 2010
ABCDEFGH
14Equity520
25Equity
Sheet1
Cell Formulas
RangeFormula
H1=SUMPRODUCT((\$C:\$C="Equity")*(\$E:\$E)*(\$B:\$B))

Last edited:

jcooooper

Board Regular
Ok, but blanks still evaluate to 0. Do you mean they're not actually blank, as in empty string "" or space " " or what?

As you can see, it's not failing as you have described the problem:

Excel 2010
ABCDEFGH
14Equity520
25Equity

</tbody>
Sheet1

Worksheet Formulas
CellFormula
H1=SUMPRODUCT((\$C:\$C="Equity")*(\$E:\$E)*(\$B:\$B))

</tbody>

<tbody>
</tbody>

Bizarre, when I type it in manually and do it on a small data set, it works.

I have about 2000 lines of data and it just errors out. Are there any other explicit rules I'm missing with sumproduct? just feels like I'm missing something easy

Joe4

Where is the data coming from?
Did you download it off of the Web, or from some other data source?
You may have some special characters messing things up (like non-breaking spaces, etc).

Also, check to make sure that you do not have any errors, text entries, or merged cells in any of those columns.
If you still cannot figure it out, please let us know the exact error message/code you are getting.

Last edited:

jcooooper

Board Regular
Where is the data coming from?
Did you download it off of the Web, or from some other data source?
You may have some special characters messing things up (like non-breaking spaces, etc).

Also, check to make sure that you do not have any errors, text entries, or merged cells in any of those columns.
If you still cannot figure it out, please let us know the exact error message/code you are getting.

Yeah I download it from an internal platform we have here, then paste as values into my sheet

Ok, so I've narrowed the problem down. When I do a find and replace on E:E, leaving the find empty and the replace 0, it works (all the blanks are filled with 0's)

So its something about those blank cells in E:E... Even when I highlight the column and convert to numbers, still doesn't work. Only when I replace the blanks with 0..... any ideas? I can manually add 0's each time though that doesn't feel efficient.

RoryA

MrExcel MVP, Moderator
It’s not a great idea to use sumproduct on entire columns, but you could use:

=SUMPRODUCT(—(\$C:\$C="Equity"),\$E:\$E,\$B:\$B)

Replies
10
Views
274
Replies
4
Views
121
Replies
14
Views
268
Replies
3
Views
180
Replies
9
Views
1K

1,171,794
Messages
5,877,565
Members
433,265
Latest member
Umaratnam

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.

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

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