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