Created attachment 140539 [details]
Example file with the same function referring to numbers formatted as text
The main problem here is the cell format which contains a number. If we use a number as a function criteria and the criteria range contains numbers but the cell format is TEXT we get a 0 result, unlike the Microsoft Office, which will return the correct result even if the cell format is text.
Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2010/2013/2016
2. Fill the A1:A3 cells with some number. Fill the B1:B3 cells with some number but modify the cell format from NUMBER to TEXT (that case the numbers will be on the left side of the cell).
3. Create two function, which contains a criteria field (for example: Countif) in the “A4” cell and one in the “B4” cell.
4. The range should be A1:A3 at the first case, and B1:B3 at the second case.
5. The criteria should be just a number in both case. (the number you set as criteria should be in the range to give back a result if you use Countif)
6. Save the file as *.xslx
7. Open the file with LibreOffice Calc.
Microsoft Excel gives back good result in both case. LibreOffice gives back good result if the cell format was number, but if the cell format was text, the result will be 0 in any case. If we use quotation marks at the criteria the function result will be good.
LibreOffice should handle the number criteria without quotation marks, if the cell format is text and the cells contains numbers, just like the Microsoft Excel.
The current behavior "breaks" results in documents that are "broken" because of numbers formatted as text - but users consider this change as "It worked before - it does not work now".
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 4; OS: Windows 6.1; UI render: default;
Locale: hu-HU (hu_HU); Calc: group
Created attachment 140540 [details]
Screenshot of the document in LO 6 and Excel 2010
*** This bug has been marked as a duplicate of bug 75834 ***