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?

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?

Yes, cells a50:a67 are empty and do not contain "x" so the sum should be 315
but is 290. If I fill the cells are filled with x then the sum is 0. If
those x(s) are then deleted the sum is now 315. The sum before filling and
then deleting the x(s) was 290. This sure seems like a bug to me.



"Don Guillett" wrote:

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?

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?

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?

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?

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?

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?

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

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?

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

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

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

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

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

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