Sumproduct() returns wrong results in Excel 2007

Working on a question submitted in an on-line forum the other day, I stumbled upon a phenomenon that threatened to do my head in.

The asker of the question had attached her workbook to the post, created with Excel 2007. As of writing this, I am working mainly with Excel 2003 and the Excel 2010 beta version. I created a solution for the question that involved Sumproduct(). I used Excel 2010 beta, tested it, and it worked fine, so I posted the spreadsheet back into the forum.

The asker still was not happy. She claimed that the formula did not calculate right. We went back and forth a few times, until I finally fired up my unloved copy of Excel 2007 and — whoa! — here was a completely different result for the Sumproduct formula.

How could that be?

I dug around a bit, asked a few people, ran a few tests and finally concluded: Excel 2007 produces wrong results with Sumproduct, depending on the syntax used, and if the precedents are volatile.

I’ve written up my findings in this article:

cheers, teylyn

 

 

Share this:

One response to “Sumproduct() returns wrong results in Excel 2007”

  1. Carla

    OMG! Thank you for posting this. I thought I was seeing things. I need to press my manager for an upgrade to 2010!!