# office-comment message

[Date Prev]
| [Thread Prev]
| [Thread Next]
| [Date Next]
--
[Date Index]
| [Thread Index]
| [List Home]

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

*From*: **Regina Henschel <rb.henschel@t-online.de>**
*To*: OASIS comment <office-comment@lists.oasis-open.org>
*Date*: Wed, 04 Jul 2012 23:18:35 +0200

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

[Date Prev]
| [Thread Prev]
| [Thread Next]
| [Date Next]
--
[Date Index]
| [Thread Index]
| [List Home]