|
Conditional Sum Incorrect
There seems to be a major bug in Excel. I open a spreadsheet in Office 2000
Excel and a conditonal sum gives a correct answer. Open the same speadsheet in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy that part of the sheet to a new sheet it returns the correct sum. The formula is of the form =sumif(a50:a67,"<x",e50:e67) sum in Excel 2000 = 315 sum in Excel 2007 = 290 Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 in Excel 2007 the sum on the status bar is correct (315). Anyone had this problem? |
Conditional Sum Incorrect
Column a50:a67 is empty (has been deleted
Sure can't equal "x" then -- Don Guillett Microsoft MVP Excel SalesAid Software "FAI_Judge" wrote in message ... There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 Excel and a conditonal sum gives a correct answer. Open the same speadsheet in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy that part of the sheet to a new sheet it returns the correct sum. The formula is of the form =sumif(a50:a67,"<x",e50:e67) sum in Excel 2000 = 315 sum in Excel 2007 = 290 Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 in Excel 2007 the sum on the status bar is correct (315). Anyone had this problem? |
Conditional Sum Incorrect
There seems to be a difference in specifications. I could only test Excel 2003 and 2007.
In Excel 2007, the criteria range has to be numeric. Blank and text cells are ignored. This was not the case in earlier Excel versions. I don't know if this should be considered a bug, but it certainly is an incompatibility! Probably some of your evaluation cells are blank or spaces, while others are zero. Can you confirm that? -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | that part of the sheet to a new sheet it returns the correct sum. The | formula is of the form | | =sumif(a50:a67,"<x",e50:e67) | sum in Excel 2000 = 315 | sum in Excel 2007 = 290 | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | in Excel 2007 the sum on the status bar is correct (315). | | Anyone had this problem? |
Conditional Sum Incorrect
Maybe I stated the problem incorrectly in the original post. If you
highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If I then fill cells a50:a67 with x the sum is 0. If I then delete the x in cells a50:a67 the sum is 315. Not good. "FAI_Judge" wrote: There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 Excel and a conditonal sum gives a correct answer. Open the same speadsheet in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy that part of the sheet to a new sheet it returns the correct sum. The formula is of the form =sumif(a50:a67,"<x",e50:e67) sum in Excel 2000 = 315 sum in Excel 2007 = 290 Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 in Excel 2007 the sum on the status bar is correct (315). Anyone had this problem? |
Conditional Sum Incorrect
Niek,
I disagree with your statement about the "criteria range." If I put a "x" in the range, the sum changes. If I delete that "x", the sum changes again. "Niek Otten" wrote: There seems to be a difference in specifications. I could only test Excel 2003 and 2007. In Excel 2007, the criteria range has to be numeric. Blank and text cells are ignored. This was not the case in earlier Excel versions. I don't know if this should be considered a bug, but it certainly is an incompatibility! Probably some of your evaluation cells are blank or spaces, while others are zero. Can you confirm that? -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | that part of the sheet to a new sheet it returns the correct sum. The | formula is of the form | | =sumif(a50:a67,"<x",e50:e67) | sum in Excel 2000 = 315 | sum in Excel 2007 = 290 | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | in Excel 2007 the sum on the status bar is correct (315). | | Anyone had this problem? |
Conditional Sum Incorrect
Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 again.
If I then delete the x's, I get the total. Several other variations of inconsistent behavior. Very interesting! However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Maybe I stated the problem incorrectly in the original post. If you | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | cells a50:a67 the sum is 315. Not good. | | | "FAI_Judge" wrote: | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | that part of the sheet to a new sheet it returns the correct sum. The | formula is of the form | | =sumif(a50:a67,"<x",e50:e67) | sum in Excel 2000 = 315 | sum in Excel 2007 = 290 | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | in Excel 2007 the sum on the status bar is correct (315). | | Anyone had this problem? |
Conditional Sum Incorrect
Niek,
This is from help in Excel 2007: Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", "32", or "apples". "<x" seems to work also. "Niek Otten" wrote: Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 again. If I then delete the x's, I get the total. Several other variations of inconsistent behavior. Very interesting! However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Maybe I stated the problem incorrectly in the original post. If you | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | cells a50:a67 the sum is 315. Not good. | | | "FAI_Judge" wrote: | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | that part of the sheet to a new sheet it returns the correct sum. The | formula is of the form | | =sumif(a50:a67,"<x",e50:e67) | sum in Excel 2000 = 315 | sum in Excel 2007 = 290 | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | in Excel 2007 the sum on the status bar is correct (315). | | Anyone had this problem? |
Conditional Sum Incorrect
Also from Help:
Range is the range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. But as I said, Excel seems to behave inconsistent. Strange things happen when you insert a row in the range, delete x's, etc. But in Excel 2007, the x's are "illegal". All things together, (compatibility, illegal values that are not flagged, inconsistent behavior, etc) I now think this is definitely a (set of) bug(s). -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Niek, | | This is from help in Excel 2007: | | Criteria is the criteria in the form of a number, expression, or text that | defines which cells will be added. For example, criteria can be expressed as | 32, "32", "32", or "apples". | | "<x" seems to work also. | | | | | "Niek Otten" wrote: | | Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 again. | If I then delete the x's, I get the total. | | Several other variations of inconsistent behavior. | | Very interesting! | | However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "FAI_Judge" wrote in message ... | | Maybe I stated the problem incorrectly in the original post. If you | | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | | cells a50:a67 the sum is 315. Not good. | | | | | | "FAI_Judge" wrote: | | | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | | that part of the sheet to a new sheet it returns the correct sum. The | | formula is of the form | | | | =sumif(a50:a67,"<x",e50:e67) | | sum in Excel 2000 = 315 | | sum in Excel 2007 = 290 | | | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | | in Excel 2007 the sum on the status bar is correct (315). | | | | Anyone had this problem? | | | |
Conditional Sum Incorrect
I think, It's a bug in XL-2007
A1:A10 (virgin blank cells) B1:B10 (B1=1, B2=2,......10) C1: =SUMIF(A1:A10,"<x",B1:B10) C1 returns 0. I enter either text (except "x") or number in column A (let say A3), C1 returns 55. When I delete cell A3. A1:A10 are blanks, C1 stay 55. I expect it returns to 0. In XL-2003 always returns 55 (either virgin blank cells A1:A10, a number, or text except "x") "FAI_Judge" wrote: There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 Excel and a conditonal sum gives a correct answer. Open the same speadsheet in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy that part of the sheet to a new sheet it returns the correct sum. The formula is of the form =sumif(a50:a67,"<x",e50:e67) sum in Excel 2000 = 315 sum in Excel 2007 = 290 Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 in Excel 2007 the sum on the status bar is correct (315). Anyone had this problem? |
Conditional Sum Incorrect
Niek,
The x's are not in the range, they are in the criteria. So they are not illegal. "Niek Otten" wrote: Also from Help: Range is the range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. But as I said, Excel seems to behave inconsistent. Strange things happen when you insert a row in the range, delete x's, etc. But in Excel 2007, the x's are "illegal". All things together, (compatibility, illegal values that are not flagged, inconsistent behavior, etc) I now think this is definitely a (set of) bug(s). -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Niek, | | This is from help in Excel 2007: | | Criteria is the criteria in the form of a number, expression, or text that | defines which cells will be added. For example, criteria can be expressed as | 32, "32", "32", or "apples". | | "<x" seems to work also. | | | | | "Niek Otten" wrote: | | Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 again. | If I then delete the x's, I get the total. | | Several other variations of inconsistent behavior. | | Very interesting! | | However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "FAI_Judge" wrote in message ... | | Maybe I stated the problem incorrectly in the original post. If you | | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | | cells a50:a67 the sum is 315. Not good. | | | | | | "FAI_Judge" wrote: | | | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | | that part of the sheet to a new sheet it returns the correct sum. The | | formula is of the form | | | | =sumif(a50:a67,"<x",e50:e67) | | sum in Excel 2000 = 315 | | sum in Excel 2007 = 290 | | | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | | in Excel 2007 the sum on the status bar is correct (315). | | | | Anyone had this problem? | | | |
Conditional Sum Incorrect
Again, inconsistency. One of the examples in Help shows "apples" as criteria, but at the same time it states the range must be
numeric. It looks like the developers were a bit in a hurry. -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Niek, | | The x's are not in the range, they are in the criteria. So they are not | illegal. | | | | "Niek Otten" wrote: | | Also from Help: | Range is the range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or | references that contain numbers. Blank and text values are ignored. | | But as I said, Excel seems to behave inconsistent. Strange things happen when you insert a row in the range, delete x's, etc. | But in Excel 2007, the x's are "illegal". | | All things together, (compatibility, illegal values that are not flagged, inconsistent behavior, etc) I now think this is | definitely a (set of) bug(s). | | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "FAI_Judge" wrote in message ... | | Niek, | | | | This is from help in Excel 2007: | | | | Criteria is the criteria in the form of a number, expression, or text that | | defines which cells will be added. For example, criteria can be expressed as | | 32, "32", "32", or "apples". | | | | "<x" seems to work also. | | | | | | | | | | "Niek Otten" wrote: | | | | Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 | again. | | If I then delete the x's, I get the total. | | | | Several other variations of inconsistent behavior. | | | | Very interesting! | | | | However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | | | "FAI_Judge" wrote in message ... | | | Maybe I stated the problem incorrectly in the original post. If you | | | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | | | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | | | cells a50:a67 the sum is 315. Not good. | | | | | | | | | "FAI_Judge" wrote: | | | | | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | | | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | | | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | | | that part of the sheet to a new sheet it returns the correct sum. The | | | formula is of the form | | | | | | =sumif(a50:a67,"<x",e50:e67) | | | sum in Excel 2000 = 315 | | | sum in Excel 2007 = 290 | | | | | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | | | in Excel 2007 the sum on the status bar is correct (315). | | | | | | Anyone had this problem? | | | | | | | | | |
Conditional Sum Incorrect
Here's where this gets to be a major problem that Microsoft might want to
address. If you start a new spreadsheet and use this formula you may notice it. However, if you open a 2003 or older spreadsheet with the formula you may never notice. I just happened to be fortunate to find it in a workbook with about 10 sheets. How does this type bug get reported to MS? "FAI_Judge" wrote: Niek, The x's are not in the range, they are in the criteria. So they are not illegal. "Niek Otten" wrote: Also from Help: Range is the range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. But as I said, Excel seems to behave inconsistent. Strange things happen when you insert a row in the range, delete x's, etc. But in Excel 2007, the x's are "illegal". All things together, (compatibility, illegal values that are not flagged, inconsistent behavior, etc) I now think this is definitely a (set of) bug(s). -- Kind regards, Niek Otten Microsoft MVP - Excel "FAI_Judge" wrote in message ... | Niek, | | This is from help in Excel 2007: | | Criteria is the criteria in the form of a number, expression, or text that | defines which cells will be added. For example, criteria can be expressed as | 32, "32", "32", or "apples". | | "<x" seems to work also. | | | | | "Niek Otten" wrote: | | Indeed it seems to be inconsistent. If I start with empty cells, I get 0 as a result. If I fill the range wit "x", I get 0 again. | If I then delete the x's, I get the total. | | Several other variations of inconsistent behavior. | | Very interesting! | | However, the "<x" is, I think, not a valid criteria in Excel 2007. I'm not sure about earlier versions. | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "FAI_Judge" wrote in message ... | | Maybe I stated the problem incorrectly in the original post. If you | | highlight the cells a50:a67 and press "delete" the sum of e50:e67 is 290. If | | I then fill cells a50:a67 with x the sum is 0. If I then delete the x in | | cells a50:a67 the sum is 315. Not good. | | | | | | "FAI_Judge" wrote: | | | | There seems to be a major bug in Excel. I open a spreadsheet in Office 2000 | | Excel and a conditonal sum gives a correct answer. Open the same speadsheet | | in Excel 2007 and the formula gives another sum. In Excel 2007 if I copy | | that part of the sheet to a new sheet it returns the correct sum. The | | formula is of the form | | | | =sumif(a50:a67,"<x",e50:e67) | | sum in Excel 2000 = 315 | | sum in Excel 2007 = 290 | | | | Column a50:a67 is empty (has been deleted). If I highlight column e50:e67 | | in Excel 2007 the sum on the status bar is correct (315). | | | | Anyone had this problem? | | | |
Conditional Sum Incorrect
Hi,
If I fill B1:B10 with 1 the sum is 10. In Excel 2007, =SUMIF(A1:A10,"<x",B1:B10) gives the incorrect value of 0 when it is first entered. If I place some text in A1, the SUMIF then calculates correctly, if I delete the text, the SUMIF continues to calculate corretly. If I delete the text, then save the workbook, close and repoen, then recalculate the SUMIF, it is incorrect It seems that the initial value of the SUMIF function is not correct. Agree this looks like a bug. Ed Ferrero www.edferrero.com |
Conditional Sum Incorrect
Hi Ed
Whilst I replied to Niek in the mvp group, that I could not re-create the problem, carrying out a save and re-open as you describe, the formula does display the incorrect value on recalculating. This was saving in compatibility mode. When I save as a .xlsx file, then reopen, it works OK. -- Regards Roger Govier "Ed Ferrero" wrote in message ... Hi, If I fill B1:B10 with 1 the sum is 10. In Excel 2007, =SUMIF(A1:A10,"<x",B1:B10) gives the incorrect value of 0 when it is first entered. If I place some text in A1, the SUMIF then calculates correctly, if I delete the text, the SUMIF continues to calculate corretly. If I delete the text, then save the workbook, close and repoen, then recalculate the SUMIF, it is incorrect It seems that the initial value of the SUMIF function is not correct. Agree this looks like a bug. Ed Ferrero www.edferrero.com |
Conditional Sum Incorrect
Great to know, thanks.
Ed Ferrero "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Ed Whilst I replied to Niek in the mvp group, that I could not re-create the problem, carrying out a save and re-open as you describe, the formula does display the incorrect value on recalculating. This was saving in compatibility mode. When I save as a .xlsx file, then reopen, it works OK. -- Regards Roger Govier |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com