Bug 103230 - SUMIF resulted wrong with blank cell criteria for data range on linked files.
Summary: SUMIF resulted wrong with blank cell criteria for data range on linked files.
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium major
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2016-10-15 04:00 UTC by Kevin Suo
Modified: 2020-07-26 10:11 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

test ods file (7.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-10-15 04:00 UTC, Kevin Suo

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2016-10-15 04:00:09 UTC
In the attached .ODS file:
Sheet1 has some data, note that the 1st column of the 3rd row is blank.
Sheet2 contains SUMIF function which has reference to Sheet1. This function is showing 0 value which is correct and is the same as other office suites.
However when copy Sheet2 to a new file, the SUMIF function returns wrong result.

Steps to Reproduce:
1. Copy "Sheet2" to a new file (right-click on the sheet name tab, then "Copy/Move Sheet - To New Document"...

Actual Results:  
SUMIF returns 123, which is the value of row #3 of the original Sheet1.

Expected Results:
SUMIF should returns 0.

Reproducible: Always

User Profile Reset: No

Additional Info:
I find this bug in version, and it still exists in version
Build ID: 01ec8f357e651ca9656837b783cf7e6a32ee4d92
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
Locale: zh-CN (zh_CN.UTF-8); Calc: group

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Comment 1 Kevin Suo 2016-10-15 04:00:52 UTC
Created attachment 128016 [details]
test ods file
Comment 2 m_a_riosv 2016-10-15 11:25:59 UTC
Version Build ID: e183d5b
Version Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24
Version Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
Build ID: ed5ca17dce1d088ce3fbbb3a30f748ba92cd07d9
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
TinderBox: Win-x86@42, Branch:master, Time: 2016-10-09_05:40:51
Locale: es-ES (es_ES); Calc: CL

Looks the issue is about how blank cell are treated for SUMIF criteria, doesn't meet the criteria with data on the same file but is taken as true when data is on a different file.

With 3.3 wasn't possible use data in other files for SUMIF.
Comment 3 m_a_riosv 2016-10-15 11:31:14 UTC
As the issue it's not really about copy/move sheet but SUMIF with reference to other files, changed the title.
Comment 4 Kevin Suo 2016-10-15 14:52:33 UTC
Seems that we have the same problem with AVERAGEIF function.
Comment 5 m_a_riosv 2017-01-19 21:55:00 UTC
*** Bug 105414 has been marked as a duplicate of this bug. ***
Comment 6 QA Administrators 2018-07-26 02:41:39 UTC Comment hidden (obsolete)
Comment 7 QA Administrators 2020-07-26 03:52:02 UTC Comment hidden (obsolete)
Comment 8 m_a_riosv 2020-07-26 10:11:49 UTC
Works for me now.
Versión: (x64)
Id. de compilación: a726b36747cf2001e06b58ad5db1aa3a9a1872d6
Subprocs. CPU: 4; SO: Windows 10.0 Build 19608; Repres. IU: predet.; VCL: win; 
Configuración regional: es-ES (es_ES); Idioma de IU: es-ES
Calc: threaded