Troubleshooting SUMPRODUCT() can be challenging at times, especially if you sometimes use the double unary operator and sometimes the multiplication syntax to coerce TRUE/FALSE values into 1 and 0 for further calculation.

Depending on which coercion method you use, you may find that you get a valid result, or an error message. Let’s look at an example.

The range to be summed contains text.

Here are a few rows and columns of sample data. We want to construct a SUMPRODUCT that sums column D if column B = 1 and column C = 2.

Sounds easy enough. Two possible ways come to mind immediately:

=SUMPRODUCT(--(B2:B6=1),--(C2:C6=2),D2:D6)

and

=SUMPRODUCT((B2:B6=1)*(C2:C6=2)*D2:D6)

But Lo and Behold! The results are not the same. The Sumproduct formula with the multiplication operator throws an error, the one with the double unary does not. Why is that?

Looking at column D again, there is a text value in the column of numbers we want to sum. Using the comma separated syntax with double unary, Sumproduct calculates fine. Using the multiplication syntax, the text value causes an error message.

Watching the formula develop with the Evaluate Formula tool, we can see that the last stage of calculation for the double unary variant is

=SUMPRODUCT({1;0;0;1;1},--{TRUE;TRUE;FALSE;TRUE;TRUE},D2:D6)

and that sails right through to the correct result of 6. But with the multiplication syntax, the last two stages are

=SUMPRODUCT({1;0;0;1;1}*D2:D6)
=SUMPRODUCT({3;0;0;#VALUE!;3})

Multiplying numbers and text this way causes the #VALUE! error.

Ranges are not the same size

Another mistake that often happens is that the ranges selected for the Sumproduct are not the same size.  Let’s look at another data sample:

The double unary variant of Sumproduct with unequal ranges could look like this:

=SUMPRODUCT--(B10:B14=1),--(C10:C15=2),D10:D14)

By mistake, we have selected one more cell in column C. The result is an error, of course, in this case the #VALUE! error.

Let’s try the multiplication syntax:

=SUMPRODUCT((B10:B14=1)*(C10:C15=2)*D10:D14)

Unsurprisingly, this also produces an error, also #VALUE!. But wait. We’ve already seen that the #VALUE! error is caused by text values in the range we want to sum. So let’s try to sum a clean range without text values:

=SUMPRODUCT((B10:B12=1)*(C10:C13=2)*D10:D12)

So, now the range to be summed is a bit shorter, does not include text, and the two ranges for the condition are of unequal size. Again, we get an error, but this time #N/A. How come? Looking again at the last stage of the Evaluate Formula tool, we can see these last two calculation steps before the end result:

=SUMPRODUCT({1;0;0;#N/A}*D10:D12
=SUMPRODUCT({3;0;0;#N/A})

So now we know where the #N/A comes from. But what about the #VALUE! when the sum range contains text? With our slightly larger range to include the text value in column D, the last two steps look like this:

=SUMPRODUCT({1;0;0;1;#N/A}*D10:D14)
=SUMPRODUCT({3;0;0;#VALUE!;3;#N/A})

The #VALUE! error shows up in the mix and takes precedence over the #NA, because it is the first error that is encountered.

With a mixed syntax that uses multiplication for the condition arrays and then the comma separator before the sum array, the error message is always #N/A.

Confused now? Here is the overview of error messages with ranges of unequal size:

Errors in the ranges

Finally, let’s look at what happens when one of the condition arrays contain an error. Here is the data sample:

In column B we have a #DIV/0 error and column C has a cell with a #REF! error. What will happen when we run a SUMPRODUCT over this?

First try:

=SUMPRODUCT(--(B19:B23=1),--(C19:C23=2),D19:D23)

The result is #DIV/0!

If we change the order of the conditions and evaluate the data in column C first, like this:

=SUMPRODUCT(--(C19:C23=2),--(B19:B23=1),D19:D23)

then we see the #REF! error as the end result. Again, the error that is first encountered in the formula is the error that will be displayed as the result.

One error that can be produced only in Excel 2003 and earlier is the #NUM! error. This one shows in a formula like this:

=SUMPRODUCT(--(C:C=2),--(B:B=1),D:D)

Prior to Excel 2007, Sumproduct can not be used with whole column or whole row references. Even with Excel 2007 and later, it is not a good idea to use whole colum references, because all the cells in the arrays will be evaluated and the arrays Sumproduct has to keep in memory will be enormous. Expect considerable calculation times when using whole column or row references with Sumproduct.

So, my lessons learned from this research:

• The comma separated syntax is more robust when Sumproduct encounters different data types in the ranges to be summed.
• #VALUE! can be the result of either
  • text values in the range to be summed (if multiplication syntax is used), or
  • ranges of unequal size
• #N/A is caused by unequally sized ranges with multiplication syntax
• The first error message encountered in Sumproduct’s calculation will be presented to the user.

If you want to play with the datasets and formulas above, please help yourself to the attached workbook in Excel 2003 format.

Sumproduct error messages.xls