none
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? :)

    2010年6月24日 10:44

全部回复

  • Good catch

    I have verified the above bug on my machine running Windows 7 and RTM Office 2010 and 2007


    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
    2010年6月24日 16:14
  • I have verified that this issue is also applicable to

    AVERAGEIF

    and

    AVERAGEIFS


    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
    2010年6月24日 16:22
  • I have verified the above bug on my machine running Windows 7 and RTM Office 2010 and 2007


    Are you saying the bug also exists in your version of Excel 2007?

    2010年6月24日 20:18
  • 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

     

    2010年6月29日 17:35
  • 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.

    2010年6月29日 19:59
  • 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
    2010年6月29日 23:57
  • 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 :-)

    2010年6月30日 20:53
  • I cannot reproduce this bug in either XL2007 or XL2010



    Regards
    Roger Govier

    "Ola Thomas Atkinson" wrote in message news:cda9fef8-8ad4-4d56-8939-49b1ae50c4e7@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.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    2010年6月30日 22:52
  • I cannot reproduce this bug in either XL2007 or XL2010



    Regards
    Roger Govier

    "nfortescue" wrote in message news:cc2d5dbf-5aa4-4725-84fd-41b0b2ccc060@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.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    2010年6月30日 22:53
  • While the "common-ness" of the situation might determine priority of fix, it it not enough that the bug exists?  After all, an O-ring 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 off-sheet 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
    2010年7月1日 6:24
  • 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

    2010年7月1日 7:09
  • PS It doesn't happen for me in 2007, I just tried at work. My 2010 at home is the home/student edition on Windows 7, not that that should make a difference.

    Nick

    2010年7月1日 7:14
  • Hi,

     

    Please make sure that there is no extra space in the data, as that might cause problems in the function.

     

    Best Regards,

     

    Sally Tang

     

    2010年7月1日 8:17
  • 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:e7d517e9-0546-4ae9-b8ab-d19fe1066a05@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.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    2010年7月1日 8:53
  • 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.)

    2010年7月1日 9:57
  • 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:e9ce0e92-825b-431a-93b1-c73ac059b015@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.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    2010年7月1日 11:33
  • 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

    2010年7月1日 11:48
  • Thank you for extra information. I have reported this issue.

    Thanks,
    Jennie Ellison
    Program Manager
    Microsoft

     

    2010年7月6日 21:48
  • Jennie, Do you have any idea how/when a fix will be implemented.  Will it be some sort of hotfix, or Office Update?

     

    Thanks

    MIKE

    2010年7月16日 13:09
  • 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 

     

     

     

    2010年7月27日 15:16
  • We have released the fix for this issue.

    http://support.microsoft.com/kb/2345345

    Thanks,

    Jennie

    2010年9月2日 13:51
  • 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:29c399f5-8100-4263-831e-42a90795e688@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.

    http://www.eset.com


    Roger Govier Microsoft Excel MVP
    2010年9月2日 18:02
  • Hi,

    Has this bug been resolved already? If so, please provide the URL were the fix can be found.

    Regards,

    lerat

    2011年3月29日 19:19
  • Jennie,

    Please explain how that article is a fix for this?  I, for one, miss the point if it is.

    Regards,
    JLatham


    I am free because I know that I alone am morally responsible for everything I do. RAH
    2011年3月29日 19:44
  • 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

    2011年8月26日 17:01
  • Hi again!

    I forgot to mention that the version where I still get the error is the 64 bits version. I just tried the 32 bit version of Excel 2010 Pro Plus and everything works fine (for now!).

     

    Thanks

    2011年8月26日 19:35
  • 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!!!
    >
    >
    >Thanks
     
    Either 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
    2011年8月27日 10:58
  • 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!!!
    >
    >
    >Thanks
     
    Either 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!!!

     

    2011年9月5日 13:47
  • 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 64-bit 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 64-bit, and not to solving your calculation problem?



    Ron
    2011年9月5日 14:31
  • 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 64-bit 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 64-bit, 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 64-bit!!! 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!

    2011年9月5日 15:14
  • 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 64-bit 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 64-bit, and not to solving your calculation problem?



    Ron

    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 64-bit!!! 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!

    Well, I suppose that since the bug was reported, and deemed fixed by the hotfix at http://support.microsoft.com/kb/2345345, that no one from MS has yet picked up on the fact that the fix isn't working in your environment.  Perhaps if you start a new thread, with a title like "Hotfix does not correct error in 64-bit Excel", or something similar, it might get the project manager's attention.

    Ron
    2011年9月5日 21:47
  • 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 Pieterse|Excel MVP|http://www.jkp-ads.com
    2011年9月6日 6:38
  • 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 Pieterse|Excel MVP|http://www.jkp-ads.com
    2011年9月6日 6:41
  • Hi Jan Karel,

    There is a way to report bugs to Microsoft:

    http://connect.microsoft.com/directory/?keywords=excel

    Hmm, this one appears to be for a limited set of (beta) products. Try the other one I posted.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    2011年9月6日 6:45
  • 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?
    2012年7月23日 15:17
  • 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?




    2012年10月11日 16:50