Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following formula that works when the ranges have more than 1 row, but generates a #VALUE error if there is only 1 row and one of the factors is blank.

=SUMPRODUCT(cost, units)

Cost    Units
   1         1
   2         1

The result is 3.

Cost    Units
   1         
   2         

The result is 0.

Cost    Units
   1         1

The result is 1.

Cost    Units
   1         

The result is #VALUE.

I can prevent the error by wrapping the formula in an IFERROR:

=IFERROR((SUMPRODUCT(cost, units)), 0)

Why does the SUMPRODUCT not work with only one row? Is there a better way to handle this than the IFERROR wrapper?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Try

=SUMPRODUCT(costs*units)

It is interesting to see in the Evaluate Formula Tool that the function goes through this stage:

enter image description here

and then presents the #Value! error. But if a cell contains the actual formula

=SUMPRODUCT(1,0)

then the result is 0. This looks like a bug.

Another possible syntax that does not present an error is

=SUMPRODUCT(--(costs),--(units))

Edit: After some more research and input from fellow Excel MVPs, here is an explanation for the behaviour described above: within Sumproduct, Excel coerces empty cells into zero values, BUT only if it's an array, i.e. a range consisting of more than one cell. A single cell range does not trigger the coercion and thus the empty cell is not coerced to 0.

The use of the multiplication operator or the double unary will also coerce the empty cell to a zero value.

It is confusing and slightly irritating, but if the input is a single empty cell that is not connected with operators or double unary,then the single, empty cell is taken at face value and will trigger the #Value! error, since it is not numeric.

share|improve this answer
    
This behavior definitely looks like a bug. Thanks for the double-dash formula. I like it better than the IFERROR version. –  Head of Catering Mar 13 '13 at 23:18
1  
It's not a bug; it's how SUMPRODUCT works. The function requires that its arguments refer to range(s) have the same "dimensions." If the 2nd argument refers to an empty cell, the function is supposed to show an error. Empty cells are different from cells that have text, '0' and spaces, and are considered null. –  Ellesa Mar 13 '13 at 23:44
    
@Kaze - I disagree. It does not show an error if the ranges contain two cells each and one range is blank. Why should it show an error if the ranges are one cell each? Also, the Evaluate Formula tool shows exactly the same intermediate steps if the second range contains a 0 or a blank. –  teylyn Mar 14 '13 at 0:19
    
@Kaze - See Brad’s answer and the comments below it. To extrapolate your comment, empty cells are different from cells with numbers in them; i.e., they are not numeric. So SUMPRODUCT should treat them as zeros, as the documentation specifies, and as it does when there is more than one row. –  Scott Mar 14 '13 at 0:33
    
I've added some more explanation above. –  teylyn Mar 14 '13 at 7:57

From the MS documentation page

Remarks

  • The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
  • SUMPRODUCT treats array entries that are not numeric as if they were zeros.
share|improve this answer
1  
The array arguments have the same dimensions in all of my examples. SUMPRODUCT is not treating non-numeric entries as if they were zeros in the case that is causing the #VALUE error. –  Head of Catering Mar 13 '13 at 18:10
1  
In your case you have something like: =SUMPRODUCT(A2,B2) where the arguments are cells rather than arrays, correct? Excel must be treating the blank cell as a null array leading to the #VALUE error. –  Brad Patton Mar 13 '13 at 19:22
    
That makes sense. I think you're right. –  Head of Catering Mar 13 '13 at 23:09
1  
As I like to say, “It’s an explanation, not an excuse.” The fact that you can explain how the code is working doesn’t mean that it’s not a bug; it means only that you understand the bug. =SUMPRODUCT(A2:A2,B2:B2) fails the same way. So I guess the bug is that Excel collapses =SUMPRODUCT(A2:A2,B2:B2) to =SUMPRODUCT(A2,B2) and then to A2*B2 –– a behavior that is not documented, as far as I can see. –  Scott Mar 14 '13 at 0:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.