询问者
BUG in Excel 2010 SUMIF & SUMIFS functions [EDIT: also AVERAGEIF & IFS]

I am quite certain that there is a bug in the implementation of the SUMIF function of Excel 2010. In certain special cases the function returns its evaluation argument instead of the correct result. This bug is reproducible in Excel 2010, but does not occur in Excel 2007 (or earlier, as far as I can tell).
The SUMIF function accepts the arguments "Range", which is the range of identifiers which the function evaluates against; "Criteria", which is the criteria that defines which members of "Range" will be chosen to sum, and "Sum_range", which is the (optional) range of values to sum. "Range" and "Sum_range" do not need to reside on the same worksheet in order for the function to return correct results.
This means that "Range" and "Sum_range" can refer to the same column of different worksheets, and this is where the bug occurs. As an example, say we have the formula =SUMIF(Sheet1!E:E;2;Sheet2!E:E). This will return 2 (i.e. the "Criteria" argument), regardless of which values are in column E of Sheet2. If we move the data in Sheet2 to column D or F, however, the same formula (e.g. =SUMIF(Sheet1!E:E;2;Sheet2!F:F) will return the correct result.
I have just noticed that the same error occurs with the SUMIFS function.
Bug resolution please? :)
 已编辑 Ola Thomas Atkinson 2010年6月25日 10:50
 已更改类型 Sally Tang 2010年7月6日 7:00
常规讨论
全部回复

How common is it to create SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS formulas where the cell references that contain the criteria range on one sheet are the same cell references for the sum or average range on the second sheet? Can you give me an example of a scenario where this type of referencing is mandatory?
In addition, can someone reply with the sample data they used for AVERAGEIFS or SUMIFS? I'm not able to reproduce the issue with those funtions.
Thanks,
Jennie Ellison
Program Manager
Microsoft 
We use this formulation on our timesheets in order to perform some error checking and make sure the correct times are billed to the correct companies. I'm sure there is a work around, but it's good to know that this is a bug as it was causing a great deal of consternation.

Jennie,
If you send me an email, I will send you a file. I sure you can get my email, even though I haven't posted it here. I've had the same email for about 10 years ending in .Net
If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire 
I've just had it too. Behaviour to reproduce described here:
 Open a blank workbook
 In sheet1, put 1 (just the number) in cells C1 to D3 (6 cells  the exact number doesn't matter)
 In sheet2, put 2,3, and 4 in cells D1, D2, and D3 respectively
 Now in a cell in sheet2 put the following formula
=SUMIF(Sheet1!D1:D3, 1, Sheet2!D1:D3)
 In another cell in sheet2 put this formula
=SUMIF(Sheet1!C1:C3, 1, Sheet2!D1:D3)
I use the formulation for dividing data into categories, and it is very odd when it works for every column except 1.
Nick Fortescue (websearch if you need to email me  I think I'm the only nick fortescue in the world :) 
I cannot reproduce this bug in either XL2007 or XL2010
Regards
Roger Govier"Ola Thomas Atkinson" wrote in message news:cda9fef88ad44d56893949b1ae50c4e7@communitybridge.codeplex.com...
I am quite certain that there is a bug in the implementation of the SUMIF function of Excel 2010. In certain special cases the function returns its evaluation argument instead of the correct result. This bug is reproducible in Excel 2010, but does not occur in Excel 2007 (or earlier, as far as I can tell).
The SUMIF function accepts the arguments "Range", which is the range of identifiers which the function evaluates against; "Criteria", which is the criteria that defines which members of "Range" will be chosen to sum, and "Sum_range", which is the (optional) range of values to sum. "Range" and "Sum_range" do not need to reside on the same worksheet in order for the function to return correct results.
This means that "Range" and "Sum_range" can refer to the same column of different worksheets, and this is where the bug occurs. As an example, say we have the formula =SUMIF(Sheet1!E:E;2;Sheet2!E:E). This will return 2 (i.e. the "Criteria" argument), regardless of which values are in column E of Sheet2. If we move the data in Sheet2 to column D or F, however, the same formula (e.g. =SUMIF(Sheet1!E:E;2;Sheet2!F:F) will return the correct result.
I have just noticed that the same error occurs with the SUMIFS function.
Bug resolution please? :)
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP 
I cannot reproduce this bug in either XL2007 or XL2010
Regards
Roger Govier"nfortescue" wrote in message news:cc2d5dbf5aa4472584fd41b0b2ccc060@communitybridge.codeplex.com...
I've just had it too. Behaviour to reproduce described here:
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)
I use the formulation for dividing data into categories, and it is very odd when it works for every column except 1.
Nick Fortescue (websearch if you need to email me  I think I'm the only nick fortescue in the world :)
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP 
While the "commonness" of the situation might determine priority of fix, it it not enough that the bug exists? After all, an Oring only needs to fail once to make a really bad day for the crew on the shuttle.
Like Alan Fast, we use a second sheet to verify charges against timesheet codes to double check that time charged against various task codes on a contract have not exceeded the authorized level, we also do offsheet checks to monitor the expenditures on contracts so that we can notify the customer when certain thresholds are encountered. Luckily we still use 2003 for our work, so the various bugs in 2007 and now 2010 haven't made our lives miserable yet (rounding anomalies when comparing column totals to a row total cause enough headaches as it is).
I am free because I know that I alone am morally responsible for everything I do. RAH 
That's very strange that you can't reproduce. Is there any way of attaching a file to this forum so I can send a simple example (though doing exactly as I described above worked for me). I believe this didn't happen for me in excel 2007, but happens every time, reliably and reproducibly in 2010.
Nick

Hi Nick
If you want to send your file direct to me, I will take a look
roger at technology4u dot co dot uk
Change the at and dots and remove spaces to make valid email address
Regards
Roger Govier"nfortescue" wrote in message news:e7d517e905464ae9b8abd19fe1066a05@communitybridge.codeplex.com...
That's very strange that you can't reproduce. Is there any way of attaching a file to this forum so I can send a simple example (though doing exactly as I described above worked for me). I believe this didn't happen for me in excel 2007, but happens every time, reliably and reproducibly in 2010.
Nick
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5241 (20100630) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP 
I confirm the bug in XL 2010, cannot duplicate in XL 2007 except when opening a workbook previously saved in 2010 (but then a Full Calc gives the correct answer).
The bug only exists when the criteria is satisfied (ie when you get a hit for the criteria against the range being searched.)

Very red face here!!!!
Yes I agree the bug exists in XL2010
I first tried it in XL2007 and it worked fine.
Being lazy, I copied the data for the 2007 workbook to a 2010 workbook, and saw the same results.
Not surprising really, as I had inadvertently pasted VALUES!!!!
Stupid boy!!
Regards
Roger Govier"cHARLES_wILLIAMS" wrote in message news:e9ce0e92825b431a93b1c73ac059b015@communitybridge.codeplex.com...
I confirm the bug in XL 2010, cannot duplicate in XL 2007 except when opening a workbook previously saved in 2010 (but then a Full Calc gives the correct answer).
The bug only exists when the criteria is satisfied (ie when you get a hit for the criteria against the range being searched.)
________ Information from ESET Smart Security, version of virus signature database 5242 (20100701) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5242 (20100701) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP 
Thank you both Roger and Charles for confirming  that saves me from waiting until I get home. I'm not very familiar with Microsoft bug reporting. Will this discussion get it picked up by the developer team or do I need to call the "report bug" support number, give my credit card details, etc. etc.?
Nick

Confirmed, this is a bug and a major one for our office. I can believe this is not receiving more attention. Probably because the heavy Excel users are still in 07 and 03.
As my office's Excel administration, I am enforcing strict 2007 Excel standardization for my entire office until this problem is fixed. The hassel of downgrading existing 2010 stations back to 2007 is well worth it for me.
Can somebody please follow up with a timeline Office Update or a hotfix? I very much would like to move everybody to 2010 when this is fixed.
Thanks
Tyler


Hi Jennie
The link takes me to How to create a and use Data Tables, not to anything related to the Bug that has been discussed in this thread.
Regards
Roger Govier"Jennie E [MSFT]" wrote in message news:29c399f581004263831e42a90795e688@communitybridge.codeplex.com...
We have released the fix for this issue.
http://support.microsoft.com/kb/282856/
Thanks,
Jennie
________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP 
Hi!
I'm currently working with excel 2010 + SP1 and I still can´t get my averageif formula to work! The formula is in sheet2 of worfile1 and it must evaluate the criteria that is in sheet1 of workfile2 and average data that is in sheet1 of workfile1.
I've tried to implement the hot fix, but it gave the error "The expected version was not found" or something like that.
I guess I must stick with Excel2007!!!
Thanks

On Fri, 26 Aug 2011 17:01:44 +0000, hobbeswithcalvin wrote:>>>Hi!>>I'm currently working with excel 2010 + SP1 and I still can´t get my averageif formula to work! The formula is in sheet2 of worfile1 and it must evaluate the criteria that is in sheet1 of workfile2 and average data that is in sheet1 of workfile1.>>I've tried to implement the hot fix, but it gave the error "The expected version was not found" or something like that.>>I guess I must stick with Excel2007!!!>>>>ThanksEither that, or use an alternative method of solving your problem which does work in your version. SUMPRODUCT or an array formula might be useful.
Ron 
On Fri, 26 Aug 2011 17:01:44 +0000, hobbeswithcalvin wrote:>>>Hi!>>I'm currently working with excel 2010 + SP1 and I still can´t get my averageif formula to work! The formula is in sheet2 of worfile1 and it must evaluate the criteria that is in sheet1 of workfile2 and average data that is in sheet1 of workfile1.>>I've tried to implement the hot fix, but it gave the error "The expected version was not found" or something like that.>>I guess I must stick with Excel2007!!!>>>>ThanksEither that, or use an alternative method of solving your problem which does work in your version. SUMPRODUCT or an array formula might be useful.
Ron
Hi!An alternative methd don't solve my problem. Although I state that the 32bit version of Excel 2010 is now corrected of this bug. I wonder why the 64bit version is not.... I have to work with large Excel files and with this king of bugs I can't get the most of 64bit Excel.
It's sad that Microsoft don't care for its customers!!!

Hi!
An alternative methd don't solve my problem. Although I state that the 32bit version of Excel 2010 is now corrected of this bug. I wonder why the 64bit version is not.... I have to work with large Excel files and with this king of bugs I can't get the most of 64bit Excel.
I'm surprised that no alternative methods of calculation will work in Excel 2010 64bit and be compatible with other versions.
So what other functions do not work?
I guess SUMPRODUCT also doesn't work when referring to a range in another workbook. That is one of the ways we used to get the same results as AVERAGEIF before that function became available in 2007. There are also array formulas that can refer to other worksheets.
Or is your problem solely related to not being able to use particular functions in Excel 2010 64bit, and not to solving your calculation problem?
Ron 
Hi!
An alternative methd don't solve my problem. Although I state that the 32bit version of Excel 2010 is now corrected of this bug. I wonder why the 64bit version is not.... I have to work with large Excel files and with this king of bugs I can't get the most of 64bit Excel.
I'm surprised that no alternative methods of calculation will work in Excel 2010 64bit and be compatible with other versions.
So what other functions do not work?
I guess SUMPRODUCT also doesn't work when referring to a range in another workbook. That is one of the ways we used to get the same results as AVERAGEIF before that function became available in 2007. There are also array formulas that can refer to other worksheets.
Or is your problem solely related to not being able to use particular functions in Excel 2010 64bit, and not to solving your calculation problem?
Ron
I've upgraded the Office version from 2007 to 2010 64bit and I want to use my earlier Excel files to calculate and update data that I need to use in another software. I noticed that the averageif and sumif formulas don't update their values if they refer to other worksheets or workfiles and after a web search I get to this forum.
I inferred that these bugs were corrected for all versions of Excel 2010… that’s not the case!
My workfiles are huge and I have to do calculations between workfiles and worksheets, update existing links, and so on… Excel 2010 64bit perform very well in my machine in terms of speed! But not with these bugs!!!
Since I have not the time to redo all my previous work (done in Excel 2007) you can say that my problem solely relates to not being able to use particular functions in Excel 2010 64bit!!! I have deadlines!!!
I suppose that Microsoft would gladly thank its customers to report bugs of its products and would try to solve them! I guess wrong!!! Sorry for that!

Hi!
An alternative methd don't solve my problem. Although I state that the 32bit version of Excel 2010 is now corrected of this bug. I wonder why the 64bit version is not.... I have to work with large Excel files and with this king of bugs I can't get the most of 64bit Excel.
I'm surprised that no alternative methods of calculation will work in Excel 2010 64bit and be compatible with other versions.
So what other functions do not work?
I guess SUMPRODUCT also doesn't work when referring to a range in another workbook. That is one of the ways we used to get the same results as AVERAGEIF before that function became available in 2007. There are also array formulas that can refer to other worksheets.
Or is your problem solely related to not being able to use particular functions in Excel 2010 64bit, and not to solving your calculation problem?
RonI inferred that these bugs were corrected for all versions of Excel 2010… that’s not the case!
My workfiles are huge and I have to do calculations between workfiles and worksheets, update existing links, and so on… Excel 2010 64bit perform very well in my machine in terms of speed! But not with these bugs!!!
Since I have not the time to redo all my previous work (done in Excel 2007) you can say that my problem solely relates to not being able to use particular functions in Excel 2010 64bit!!! I have deadlines!!!
I suppose that Microsoft would gladly thank its customers to report bugs of its products and would try to solve them! I guess wrong!!! Sorry for that!
Ron 
Hi Hobbeswithcalvin,
I've upgraded the Office version from 2007 to 2010 64bit and I want
to use my earlier Excel files to calculate and update data that I
need to use in another software. I noticed that the averageif and
sumif formulas don't update their values if they refer to other worksheets
or workfiles and after a web search I get to this forum.There is a way to report bugs to Microsoft:
http://connect.microsoft.com/directory/?keywords=excel
Regards, Jan Karel PieterseExcel MVPhttp://www.jkpads.com 
Hi Hobbeswithcalvin,
I suppose that Microsoft would gladly thank its customers to report
bugs of its products and would try to solve them! I guess wrong!!!
Sorry for that!Go here:
http://support.microsoft.com/gp/contactbug
Regards, Jan Karel PieterseExcel MVPhttp://www.jkpads.com 
Hi Jan Karel,
There is a way to report bugs to Microsoft:
http://connect.microsoft.com/directory/?keywords=excelHmm, this one appears to be for a limited set of (beta) products. Try the other one I posted.
Regards, Jan Karel PieterseExcel MVPhttp://www.jkpads.com 
I enter orders for goods. Each row gives the date, the buyer, the item, and the price. All that is on sheet A. I want to get summaries, so on sheet B I have a bunch of "SUMIFS" so I can get summaries by quarter, buty month, by year and I want to also be able to get them by type (appliances, supplies, computers...) or by customer. So it is natural to want to put the classifying information on sheet B rather than on sheet 1. Does that give you enough of a reason for wanting it to work correctly?

I'm experiencing this problem on Excel 2010. I went to install the hotfix and the installer told me it was already installed. Now what?
Here's the Formula:
=SUMIF(A8:Z8,">="&A$3,A9:Z9)
If A8:Z8 are static date values (1/1/2012) that we manually created in the current spreadsheet, the formula works. If A8:Z8 reference data based on a Pivot Table, I get 0 back. Even if the pivot data was copied and value pasted into another work sheet!?!
[EDIT]  I also found that when you copy a row of standard dates from a Pivot Table into a new spreadsheet and use the cell handle of the first cell to drag right (effectively overwriting existing dates), it increments the years instead of the days which is odd. There is some special formatting associated with the data that is not apparent.
Any help on this? Work arounds?
 已编辑 Alex Scott 2012年10月11日 21:48