SUMPRODUCT and Blank Cells

[imback2nite]

Hello everyone and thank you for looking at this question. I’m using, or should I say, attempting to use SUMPRODUCT.
I’m trying to use this formula. The problem I’m having is there are blank cells in columns ‘A’, ‘B’, ‘C’ and the ‘D:AA’. How can I negate the blank cells?

=SUMPRODUCT(--(Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[Anonymous1378]

Perhaps this will do?

=SUMPRODUCT(IFERROR(--(Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13),0))

 

[imback2nite]

Now I'm getting a #NAME? error.

 

[Peter_SSs]

@Anonymous1378
IFERROR was introduced with Excel 2007 I believe. In that case the OP's version in their profile would mean that they do not have access to that function.

 

[Anonymous1378]

Apologies, I didn't realize IFERROR was 2007+ only.

 

[Peter_SSs]

@imback2nite

• What is the name of the sheet that this formula is on (even if the suggestion below works)
  
  
• Blank cells should not cause a problem I think. Do you mean cells that contain formulas that return "" so that the cells look blank even though they are not?
  
  
• Does this formula work for you?

Try

=SUMPRODUCT(IF(ISERROR(--(Finances!$D$36:$AA$1000)),0,--(Finances!$D$36:$AA$1000))*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[imback2nite]

I'm sorry. I should have specified. I'm using the dinosaur version. Excel 2003. With the new formula I'm getting a 0 instead of a correct sum.
The formula will reside on the Chart page. I do not get a "", at least not yet.
There are no blank cells ranging from $36 to $38 and I change the formula accordingly but I still get a 0 sum.

 

[Peter_SSs]

The formula will reside on the Chart page.

Thanks. In that case you definitely should remove both references to the Chart sheet from your formula. It is not needed and can actually lead to incorrect results in some cases.

Here is a very small set of sample data that I tested with. Notice the column of 'null' formulas in column F

imback2nite.xlsm
	A	B	C	D	E	F	G
13			x
14
35
36	a	2	x	1	8
37	b	3	y	2	3
38	a	3	y	3	2
39	b	3	y	4	1
40	a	2	x	5	3
41	b	2	x	6	3
42
Finances

Cell Formulas
Range	Formula
F36:F41	F36	=""

Here is my suggested formula (with the Chart sheet names removed) in F5 and your original formula in G5. The result of my formula is the sum of the green cells above. Is that what you would be expecting?
If so, then it would seem that there is something different about the nature of your data compared to mine. As a starting point, to test the concept in your Excel version, open a brand new workbook and manually enter the values the same as mine then enter the F5 formula from below and see if you also get 17.

Also, you did not mention originally what was going wrong with your formula. Was it returning a #VALUE! error like below?

imback2nite.xlsm
	D	E	F	G
5	a	2	17	#VALUE!
Chart

Cell Formulas
Range	Formula
F5	F5	=SUMPRODUCT(IF(ISERROR(--(Finances!$D$36:$AA$1000)),0,--(Finances!$D$36:$AA$1000))*(Finances!$A$36:$A$1000= $D$5)*(Finances!$B$36:$B$1000=$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))
G5	G5	=SUMPRODUCT(--(Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[Phuoc]

Not tested:

=SUMPRODUCT((0&Finances!$D$36:$AA$1000)*(Finances!$A$36:$A$1000= Chart!$D$5)*(Finances!$B$36:$B$1000=Chart!$E$5)*(Finances!$C$36:$C$1000=Finances!$C$13))

 

[imback2nite]

I'm sorry. Still getting 0. And Phuoc, Thanks but now getting #VALUE error.