SUMPRODUCT error when blank cell

[jcooooper]

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

 

[Scott Huish]

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

 

[jcooooper]

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

 

[jcooooper]

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

 

[Scott Huish]

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
	A	B	C	D	E	F	G	H
1		4	Equity		5			20
2		5	Equity
Sheet1

Cell Formulas
Range	Formula
H1	=SUMPRODUCT(($C:$C="Equity")*($E:$E)*($B:$B))

 

[jcooooper]

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

	A	B	C	D	E	F	G	H
Equity
Equity

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=SUMPRODUCT(($C:$C="Equity")*($E:$E)*($B:$B))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[jcooooper]

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]

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

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