This is a peculiar phenomenon — maybe even a bug?

SUMPRODUCT() is often used with the double unary operator to coerce the TRUE/FALSE values of a condition into 1 or 0 respectively to be used in further calculations. If you are using Excel 2007, you may want to break that habit, depending on whether or not the precedents of the SUMPRODUCT formula are volatile.

In the attached file, you can see  static data in column B, and in column C some data that is calculated using the RAND() function. So the data in column C is volatile, i.e. it will recalculate whenever Excel recalculates the sheet. The easiest way to make Excel do that is to hit F9.

Column G has a SUMPRODUCT formula using the multiplication operator:

=SUMPRODUCT(($B$3:$B$26=$E3)*($C$3:$C$26>50))

Column F has a SUMPRODUCT formula using the double unary operator:

=SUMPRODUCT(--($B$3:$B$26=$E3),--($C$3:$C$26>50))

None of these formulae are rocket science, and you’d expect them to return the same results.

Well — not quite.

When the attached file is opened in Excel 2003 or Excel 2010 beta, all is well. Everything calculates just fine and re-calculates on hitting F9.

But — if the file is opened with Excel 2007, the calculations in column F will not re-calculate.

Excel does not bemoan the formula or hint at any error. In fact, if you edit any cell in column F and confirm again with enter, it will indeed recalculate, but only just the once.

This is important to know if you have Excel 2003 workbooks that you want to port to Excel 2007 any time soon. Check if your SUMPRODUCT formulae …

• use double unary
• are based on a volatile precedent

… and if they do, try to change them to the * operator before starting to use them in Excel 2007.

PS: After testing and saving the workbook in 2007, I opened it again in 2003 and found that the column F would now not calculate here, either. But after manually editing and confirming the formula in cell F3 and copying it down, all was good again.

PPS: Further checking around in the online community confirms that Excel 2007 has problems with the unary minus when used with volatile precedents. The problem is not limited to SUMPRODUCT(), but will extend to any array type formula that uses the unary minus and relies on volatile precedents. Another unary operator, the %, shows the same problem, whereas the binary arithmetic operators + – * ^ / all produce correct results.

Attachment: XL07 Sumproduct bug.xls

created: 21 December 2009