GNOME Bugzilla – Bug 686156
SUMIF
Last modified: 2013-04-16 19:00:34 UTC
This is extracted from https://tools.oasis-open.org/issues/browse/OFFICE-3764. It is claimed there that: 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. apparently there are some differneces between Excel & Gnumeric behaviour. (I have not verified these differneces.)
[1] =SUMIF(A1:A2;"=";B1:B2) results in what? [2] =SUMIF(A1:A2;"="&A3;B1:B2) results in what? These are identical by the time SUMIF sees them. Everyone agree on that. [3] =SUMIF(A1:A2;"";B1:B2) results in what? This is different: [3] is a pattern match for the empty pattern; [1] and [2] would string comparisons, except that XL treats "=" and "<>" all by themselves special: they test for emptiness. This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report.