views:

48

answers:

3

I've just upgraded to Excel 2010 (it was free as I bought 2009 recently). I now don't have another version of excel to test it on but I think I've discoved a SUMIF bug. Now I know the chances of me discovering a bug in excel are almost zero, so can someone tell me what I'm doing wrong, or alternatively tell me what their version of Excel does?

  1. Open a blank workbook
  2. In sheet1, put 1 (just the number) in cells C1 to D3 (6 cells - the exact number doesn't matter)
  3. In sheet2, put 2,3, and 4 in cells D1, D2, and D3 respectively
  4. Now in a cell in sheet2 put the following formula =SUMIF(Sheet1!D1:D3, 1, Sheet2!D1:D3)
  5. In another cell in sheet2 put this formula =SUMIF(Sheet1!C1:C3, 1, Sheet2!D1:D3)

As far as I can see, the cells should display the same value, but in my version of excel, the first displays 3 (incorrectly) and the second displays 9 (correctly - the sum of 2,3 and 4.)

The values 1,2,3, and 4 are irrelevant. It seems to be the fact that the letters for the columns match that causes the bug.

Before anyone argues Excel isn't programming I beg to differ.

[ I have since found another bug report on a technet forum: http://social.technet.microsoft.com/Forums/en/excel/thread/cda9fef8-8ad4-4d56-8939-49b1ae50c4e7 - so it might be real ]

A: 

Excel 2003

No. 4 = 9

No. 5 = 0

These two formulas would return the same thing

=SUMIF(Sheet1!D1:D3,1,Sheet2!D1:D3)
=SUMIF(Sheet1!C1:C3,1,Sheet2!D1:D3)

which it seems is what you're driving at.

Dick Kusleika
Sorry, I typoed, fixed now.
Nick Fortescue
+2  A: 

Yes, it is a bug in Excel 2007 and 2010 SUMIF and SUMIFS (and related functions).

Charles Williams
can you give me a reference on microsoft's site? Or a workaround? Thanks.
Nick Fortescue
AFAIK there is no reference on a Microsoft Site to this bug.The bypasses I know are to use different columns or roll-your-own SUMIF.
Charles Williams
Correction: the bug does not exist in XL 2007, except temporarily when you open a workbook with the bug that was created in XL 2010 - the the first recalculation of the buggy formula fixes it.
Charles Williams
A: 

I've pursued more investigation - it is a bug in Excel 2010 (but not 2007), and Microsoft have now acknowledged it here: SUMIF bug excel 2010. See the posts from Jenny E. No word on a fix yet.

Nick Fortescue