SUMPRODUCT error when blank cell

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 17, 2004
Messages
19,958
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
74
Office Version
  1. 365
Platform
  1. Windows
Sorry - columns B and E contain a mix of values and empty cells
 

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Mar 24, 2018
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
64,700
Office Version
  1. 365
Platform
  1. Windows
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
74
Office Version
  1. 365
Platform
  1. Windows
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
39,068
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)
 

Forum statistics

Threads
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.
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
Top