Cannot resolve parameter type "Criterion", which is used in SUMIF e.g.

[Regina Henschel <rb.henschel@t-online.de>]

Hi all,

the rules for "Criterion" are not clear enough to determine the correct 
result.

First problem
=============
A1 contains the string "1234".
A2 contains the number 1234.
B1 contains the number 1.
B2 contains the number 2.

HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL is true.


=SUMIF(A1:A2;"1234";B1:B2) results in what?

In section '4.11.8 Criterion' I read
<quote>
A criterion is a single cell Reference, Number or Text. It is used in 
comparisons with cell contents.
</quote>
So it seems clear "1234" is a text, only A1 matches, because A2 is a 
number. Indeed a single comparison (A1="1234") results in TRUE, and the 
single comparison (A2="1234") results in FALSE. So I expect result 1 
from B1.
Unfortunately, all three Microsoft Excel, Apache OpenOffice and Gnumeric 
result in 3, which means that they consider A2 to match too.

In the case =SUMIF(A1:A2;1234;B1:B2) which should match cells with 
number 1234, Gnumeric and Microsoft Excel results in 3, Apache 
OpenOffice results in 2.

Second problem
==============
Section 4.11.8 contains a description of what is there called 'matching 
expression'. It is the only place, where I find the term 'matching 
expression' and its connection to 'Criterion' is not clear.

From the use in existing spreadsheet applications I guess, that it is 
intended that something like  >=1234  should also be possible in SUMIF. 
But details are missing.

My interpretation: The second parameter of SUMIF can be a single number, 
a string, a reference to a single cell, or a 'matching expression'. A 
'matching expression' is a string that starts with a comparison operator 
followed be a number, or starts with the sign = or the sign <> followed 
by a string or followed by nothing (to catch empty cells). All these are 
valid 'Criterion'. Notice, that the interpretation of "=" would be 
ambiguous.

What interpretation is intended?

Third example
=============
A1 contains an empty string, for example generated by the formula ="".
A2 is empty
A3 contains the formula ="" which results in an empty string

B1 contains the number 1.
B2 contains the number 2.


=SUMIF(A1:A2;"=";B1:B2) results in what?
With the interpretation, that the second parameter is a 'matching 
expression' and the description of this case in section 4.11.8, it 
matches A2 and does not match A1, result is 2.
With the interpretation, that the second parameter is the literal 
character =, it matches neither A2 nor A1, result is 0.
Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.

=SUMIF(A1:A2;"="&A3;B1:B2) results in what?
With the interpretation, that the second parameter is a 'matching 
expression' it matches A1 and does not match A2, result is 1.
Gnumeric calculates 3, Excel calculates 2, AOO calculates 1.

=SUMIF(A1:A2;"";B1:B2) results in what?
With the interpretation, that the second parameter is a string (the 
empty string) it matches A1 and does not match A2, result is 1.
Gnumeric and Excel calculate 3, AOO calculates 1.


I hope the examples show, where I see shortcomings in the current text.

Kind regards
Regina