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

Copied from office-comment list
Original author: Regina Henschel
Original date: Wed, 04 Jul 2012 23:18:35 +0200
Original URL (for information only - broken link): https://lists.oasis-open.org/archives/office-comment/201207/msg00000.html

New URL (2024): https://groups.oasis-open.org/communities/community-home/digestviewer/viewthread?MessageKey=96a207be-8abf-438a-989d-98b3b4407aff&CommunityKey=96966655-9ba3-4069-82be-018f5aa7f7f2#bm96a207be-8abf-438a-989d-98b3b4407aff

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.