SUMPRODUCT error when blank cell

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
52
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If columns B & E are actually empty, that would equate to 0 in your formula. So what is your problem exactly?
 

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
52
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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style=";">Equity</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style=";">Equity</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$C:$C="Equity"</font>)*(<font color="Red">$E:$E</font>)*(<font color="Red">$B:$B</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
52
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 24, 2018
Messages
52
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
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It’s not a great idea to use sumproduct on entire columns, but you could use:

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

Watch MrExcel Video

Forum statistics

Threads
1,108,855
Messages
5,525,227
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top