Problem description: When using the SUMIF function through the function wizard, a Criterion with text value is being marked with a single quotation mark, which results in a 0 value of the sum. When the single quotation mark is manually being changed to a double quotation mark in the formula, the function works correctly. Example: =SUMIF(E2:E8;'Paid';C2:C8) is the formula gained through use of the wizard. This will result in an incvorrect sum When being corrected to =SUMIF(E2:E8;"Paid";C2:C8), the result is correct. Steps to reproduce: 1. Add the following data to a spreadsheet: A1 = 1; A2 = 2, A3 = 3, A4 = 4, A5 = 5 B1 = Paid; B2 = Unpaid; B3 = Paid; B4 = Unpaid; B5 = Paid 2. Enter a SUMIF formula through the function wizard in C1 with the following arguments: Range = B1:B5 Criteria = Unpaid Sum range = A1:A5 Current behavior: Formula =SUMIF(B1:B5;'Unpaid';A1:A5) is generated, the result gained in C1 is 0 Expected behavior: The result in C1 should be 6 This can be obtained by manually changing 'Unpaid' to "Unpaid" in the formula. Platform (if different from the browser): Ubuntu 12.04 LTS Browser: Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:12.0) Gecko/20100101 Firefox/12.0
Confirmed in 3.6.1.2 (Windows)
Still and issue with Calc 4.5.
I'm not sure if this is the same issue or not, but if you type a double quote into a cell in Calc, they look like this: “”. But if you type double quotes directly in the FIND (search?) box in Calc, they look like this: "". The result is that any search using double quotes will FAIL, because the characters are NOT the same. Furthermore, if anyone cuts and pastes this into Python, and perhaps other programs, they will have untold grief as I have had, but I never suspected Calc was the source of my problem until now, when I did this double quote search and knew a zero result was wrong.
This is not a bug. If you want to match the string "Unpaid" then you have to enter it as string "Unpaid" including the double quotes, or use a cell reference to a cell that contains the string. When entered without double quotes the Unpaid is taken as a) a defined name, or b) if there exists no name like in this case a label looked up in B2, being a row label in this case with the formula in C1 the implicit intersection at C2 is formed as reference and the value in C2 (in this example 0) is the criterion. The label ends up in single quotes because that is the canonical form of a label, in case the string value in B2 is changed the label in the formula follows that and if it includes whitespace then it has to be enquoted in single quotes. In newer releases the "Automatically find column and row labels" option under Tools -> Options -> Calc -> Calculate is disabled, because it leads to confusion and maybe unexpected results, so in this example the formula results in #NAME? error. Only labels within defined label ranges are used per default, but not looked up from the sheet if not defined.