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
| 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
| >
| >
| > | 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
| > | >
| > | >
| > | > | 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?
| > | >
| > | >
| > | >
| >
| >
| >