Bug 96923 - Criteria evaluation of COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
Summary: Criteria evaluation of COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2016-01-06 14:46 UTC by Klaibson Ribeiro
Modified: 2020-05-28 23:22 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
The table in trouble, for analysis. The password is CUSTOS2014 (7.11 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-01-06 14:46 UTC, Klaibson Ribeiro
Details
Simple Test XLSX file (7.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-01-08 01:34 UTC, Kevin Suo
Details
Screenshot showing the difference opening the Simple Test File in MSO 2010 and LibreOffice (146.76 KB, image/png)
2016-01-08 01:36 UTC, Kevin Suo
Details
Inconsistency between comparison order (5.05 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-05-28 23:12 UTC, Martin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Klaibson Ribeiro 2016-01-06 14:46:04 UTC
Created attachment 121748 [details]
The table in trouble, for analysis. The password is CUSTOS2014

All formulas work, minus the SUMIFS function does not work in an imported spreadsheet of Microsoft Excel 2007 to LibreOffice Calc 5.0.3.  The main error is the Esc. Ant tab. Vanya.
Comment 1 Buovjaga 2016-01-06 19:23:03 UTC
In which sheets / cells can we see the SUMIFS?
Comment 2 GerardF 2016-01-06 20:26:06 UTC
Not easy to search with such a big file...

But I saw the difference of calculation.
On sheet 'ESC. ANT. VANYA' (Sheet 82 on 109 Column F for example), You have the following formula in F11 :
=SUMIFS($'12-14'.D:D;$'12-14'.C:C;1;$'12-14'.A:A;B11)-SUMIFS($'12-14-LS'.D:D;$'12-14-LS'.C:C;1;$'12-14-LS'.A:A;B11)
The part of the formula causes the problem is $'12-14'.C:C;1
the formula search for the numeric criterion 1 in column C of sheet '12-14' but this column contains the text '1.

Calc don't count the text 1 with SUMIFS having creterion 1 (as number).

If I select column C on sheet '12-14' and make Data > Text to colums, data is converted to numeric. The same for sheet '12-14-LS'.
Now I get the correct values.

But I'm not sure this is really a bug...
should we count all the 1 whatever they are text or numeric ?
Comment 3 Buovjaga 2016-01-07 12:35:21 UTC
erAck commented on IRC that we should find out if Excel is treating "strings as number" differently in SUM and SUMIFS.
Comment 4 Joel Madero 2016-01-07 16:50:39 UTC
This big of a file is just about useless - we need a trimmed down version with JUST the pertinent info. I just spent 10 useless minutes trying to look through this file to try to triage it (I have Excel) and it was a waste of time.

Please provide a better test case - a single formula, 2 sheets max.


Thanks!
Comment 5 Klaibson Ribeiro 2016-01-07 16:54:07 UTC
Good afternoon.

The error occurs mainly in Esc. Ant spreadsheet. Vanya.

The spreadsheet is full of links, so I did not send only a small file.

Have a nice week.
Comment 6 Pedro 2016-01-07 17:13:18 UTC
I opened the file in Excel 2010 and LO 5.0.4.2 (and 5.1.0.1) and the results on sheet 'ESC. ANT. VANYA' are exactly the same.

Apparently the bug has been fixed.

Answering erack's question, all the SUMxxx functions return the same value under Excel 2010 (using the example provided by Gérard Fargeot in the QA mailing list)

Hope this helps
Comment 7 Joel Madero 2016-01-07 17:59:52 UTC
Hi,

> 
> The error occurs mainly in Esc. Ant spreadsheet. Vanya.
> 
> The spreadsheet is full of links, so I did not send only a small file.
> 
> Have a nice week.

The point is we need you to create a fresh document that is much simpler and demonstrates the same "bug" (if it is in fact a bug). Providing these ultra-complex documents makes triaging problems hard, and fixing problems even harder.
Comment 8 Klaibson Ribeiro 2016-01-07 18:55:01 UTC
Good Afternoon.

Problem resolved. The suggestion of Gerard was correct.

Good week.

Thanks.
Comment 9 Kevin Suo 2016-01-08 01:34:16 UTC
Created attachment 121790 [details]
Simple Test XLSX file

Attached is a simple test file provided by Gérard Fargeot in the QA mailing list:
http://nabble.documentfoundation.org/QA-Need-someone-having-Excel-td4171047.html#a4171083

When open this file with Microsoft Excel and with LibreOffice, the calculated results are different.
Comment 10 Kevin Suo 2016-01-08 01:36:15 UTC
Created attachment 121791 [details]
Screenshot showing the difference opening the Simple Test File in MSO 2010 and LibreOffice
Comment 11 Pedro 2016-01-08 12:26:41 UTC
(In reply to Kevin Suo from comment #10)
> Created attachment 121791 [details]
> Screenshot showing the difference opening the Simple Test File in MSO 2010
> and LibreOffice

I can confirm these differences. The results obtained by LibreOffice are consistent with Apache OpenOffice. 

The question here is: does TDF want to follow the Microsoft lax logic (assume a text number was a mistake) or follow a strict logic (a text number is deliberate).

If the results should be the same as Excel's then this is a bug.

This is a philosophical discussion...
Comment 12 Eike Rathke 2016-07-11 16:41:43 UTC
Excel in itself is iconsistent. Take for example the formula =SUMIF($B$3:$B$6,1,$C$3:$C$6) in B16. This apparently evaluates every B3:B6=1 as TRUE and thus sums all values in C3:C6 to 10. But, for =SUMIF($B$3:$B$6,1,$B$3:$B$6) the result is 2 and not 4 so it either sums only the numeric values in B3:B6 or only evaluates the numeric values in B3:B6 in the condition as TRUE. Same for =SUMIF($B$3:$B$6,1)
Comment 13 Eike Rathke 2016-07-11 17:03:25 UTC
Brilliant.. in the sum-range only numeric content is taken (same as in SUM, so at least consistent there), but in the condition it does not care about numeric or not.
Comment 14 Eike Rathke 2016-07-11 17:10:47 UTC
That at least seems to be consistent among all related functions, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS.
Comment 15 QA Administrators 2018-07-26 02:41:12 UTC Comment hidden (obsolete)
Comment 16 Martin 2020-05-28 23:12:19 UTC
Created attachment 161385 [details]
Inconsistency between comparison order
Comment 17 Martin 2020-05-28 23:22:24 UTC
Confirmed in LibreOffice 6.4.3.2.

See the attachment "Inconsistency between comparison order".

I get the following results:

1) If comparing a number to a number (2nd and 3rd argument to SUMIFS), we get a match as expected.

2) If comparing a number to a string, we get a match.

3) If comparing a string to a number, we DON'T get a match.

4) If comparing a string to a number, but with an explicit equality operator ("="&) we get a match.

In Excel, all cases (1-4) result in a match. Because of the unexpected inconsistency, I would say that this is indeed a bug, and that the Excel behavior of having all four cases result in a match should be followed.