Bug 117433 - COUNTIF() doesn't properly count empty cells for empty ("" or "=") criteria
Summary: COUNTIF() doesn't properly count empty cells for empty ("" or "=") criteria
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:6.1.0 target:6.0.5
Keywords:
: 118014 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-05-04 23:23 UTC by exceller
Modified: 2018-06-13 07:55 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Count.If from Excel 2010 (8.62 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-05-05 13:59 UTC, Jacques Guilleron
Details
Same file writed with Calc (8.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-05 14:04 UTC, Jacques Guilleron
Details
screenshot (90.36 KB, image/png)
2018-06-12 11:46 UTC, Xavier Van Wijmeersch
Details
countif issue (12.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-06-12 14:19 UTC, Xavier Van Wijmeersch
Details

Note You need to log in before you can comment on or make changes to this bug.
Description exceller 2018-05-04 23:23:09 UTC
Description:
tested count.if(A1:B3;"") and count.if(A1:B3;"<>S") with blank cells but return 3 instead of 6.

Steps to Reproduce:
1.Write the formula in description
2.
3.

Actual Results:  
In a range of 6 cells it count only 3 instead of 6.

Expected Results:
It shoud count them all.


Reproducible: Always


User Profile Reset: No



Additional Info:
Versione: 6.0.3.2 (x64)
Build ID: 8f48d515416608e3a835360314dac7e47fd0b821
Thread CPU: 8; SO: Windows 10.0; Resa interfaccia: GL; 
Versione locale: it-IT (it_IT); Calc: CL


User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36
Comment 1 Jacques Guilleron 2018-05-05 13:59:49 UTC
Created attachment 141905 [details]
Count.If from Excel 2010
Comment 2 Jacques Guilleron 2018-05-05 14:04:13 UTC
Created attachment 141906 [details]
Same file writed with Calc
Comment 3 Jacques Guilleron 2018-05-05 14:46:18 UTC
Hi exceller,

I reproduce with
LO 6.1.0.0.alpha1+ Build ID: 936eaedddbc6d21737745be3c3131607440e366c
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-05-04_01:09:04
Locale: fr-FR (fr_FR); Calc: CL
also with
LO 4.3.0.0.beta1
Build ID: b7cfa1eab1cb1e94f71d6df6612b73f231d0bf92
I Don't reroduce with
LO 4.2.8.2 Build ID: 48d50dbfc06349262c9d50868e5c1f630a573e
where I get 0 as result of =NB.SI(A1:B3;"")
Try to import from Excel 2010, I get and keep the right result, even after recalculate. See first attachment.
The same file writed with Calc doesn't give the result. See second attachment.
Thank you to have reported it.
Comment 4 m.a.riosv 2018-05-06 16:17:19 UTC
Seems only works for those formulas with the first column, but works searching for other values.
Comment 5 Xisco Faulí 2018-05-07 14:33:56 UTC
I can also reproduce it in


Version: 4.3.0.0.alpha1+
Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

using the .ODS file
Comment 6 Winfried Donkers 2018-05-07 14:59:53 UTC
I will have a look at it to see if I can find the cause.
Comment 7 Winfried Donkers 2018-05-08 07:52:13 UTC
I know now where the problem is caused. 
Next step is finding a solution, which may not be that easy.

The problem only occurs when complete columns (except the first) are empty.
E.g. it will ignore all empty cells but the first when you use a row as range.
Comment 8 Commit Notification 2018-05-09 15:05:01 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1a8febcaa4f932124d417877a3b08bd059c0362d

tdf#117433 count empty cells properly with COUNTIF.

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2018-05-11 07:59:56 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9b2ebcfe80900b2f6299851d3a271bdcaa85049a&h=libreoffice-6-0

tdf#117433 count empty cells properly with COUNTIF.

It will be available in 6.0.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 exceller 2018-06-05 17:00:54 UTC
*** Bug 118014 has been marked as a duplicate of this bug. ***
Comment 11 Xavier Van Wijmeersch 2018-06-05 17:25:36 UTC
I think its need to be reopen because its not working with

Version: 6.1.0.0.alpha1+
Build ID: 1462bdc3abe60cee43cf6064663d646adbf13894
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-05-19_02:52:14
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 6.2.0.0.alpha0+
Build ID: bfed5c384d41253bec39fc3e44f839242602f1a1
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-06-03_00:35:28
Locale: nl-BE (en_US.UTF-8); Calc: group threaded

Version: 6.2.0.0.alpha0+
Build ID: 35c00b2ece11b7f60c5ffba10bd7083d4e7bc4f2
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 12 exceller 2018-06-05 17:36:44 UTC
I have tested the latest version with the fix for windows and it works...
So, thanks a lot for the fix. Other platform still have this problem and many more. LibreOffice is the best. Great work, keep it up.
Comment 13 Xisco Faulí 2018-06-05 17:50:25 UTC
So it seems the .ODS still shows 3 instead of 6

Version: 6.2.0.0.alpha0+
Build ID: 48b49937fed5e50d299a94063eb325799ff672e9
CPU threads: 1; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-06-05_01:47:06
Locale: es-ES (es_ES); Calc: group threaded
Comment 14 Winfried Donkers 2018-06-06 05:54:33 UTC
I will try to find out what causes this.
The fix includes an automatic test at build time to check that this particular problem does not occur...
Comment 15 Winfried Donkers 2018-06-12 06:52:41 UTC
Current master, built on Linux (64bit) produces 6 with COUNTIF(A1:B3;"").

Daily build of version 6.0.6.0.0+ (x64), build time 2018-06-12 02:02:13 on Windows10 produces 6 with COUNTIF(A1:B3;"").

Daily build of version 6.1.0.0beta1 (x64), build time 2018-06-12 04:32:21 on Windows10 produces 6 with COUNTIF(A1:B3;"").

Pre release version 6.0.5.1 on Windows10 produces 6 with COUNTIF(A1:B3;"").

Conclusion: I can not see a reason why this bug report is reopened.

@Xavier, Xisco : can you still reproduce the problem with a current version 6.0.5 or higher? And if so, what are the steps to reproduce the problem?
Comment 16 Jacques Guilleron 2018-06-12 09:01:03 UTC
Hi all,

With the file I provided(Same file...) , I have to use F9 to get COUNTIF(A1:B3;"")
correctly working, or rewrite the formula. But this xorks with
LO 6.2.0.0.alpha0+ Build ID: 7530424771c84d50f1e7e9ec5eba0bffc91d655a
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-06-06_05:38:42
Locale: fr-FR (fr_FR); Calc: CL
and 
LO 6.1.0.0.beta1 Build ID: 8c76dfe1284e211954c30f219b3a38dcdd82f8a0
Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
Thank you very much for the patches, Winfried.
Comment 17 Winfried Donkers 2018-06-12 09:56:25 UTC
(In reply to Jacques Guilleron from comment #16)
> Hi all,
> 
> With the file I provided(Same file...) , I have to use F9 to get
> COUNTIF(A1:B3;"")
> correctly working, or rewrite the formula. [...]

That is correct. As the file was saved by LibreOffice and opened again by LibreOffice, there is no need to recalculate all formulas on opening.
Changing the cells of the input range, or the formula, or pressing F9 will make LibreOffice to recalculate the formula.
Comment 18 Xavier Van Wijmeersch 2018-06-12 11:46:25 UTC
Created attachment 142676 [details]
screenshot

I can still reproduce the problem, see screenshot, with home build LO 20180611

Version: 6.2.0.0.alpha0+
Build ID: 807d4382cb021d2ac3ea99d6757a7b368a32941d
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 19 Xavier Van Wijmeersch 2018-06-12 12:07:28 UTC
found out countif(A1:B3;"") is not working nor with F9 or hard calculation
but with countif(A1:B3;"<>=") then i have the correct value 6 without recalculation

Version: 6.2.0.0.alpha0+
Build ID: 807d4382cb021d2ac3ea99d6757a7b368a32941d
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 20 Winfried Donkers 2018-06-12 13:06:18 UTC
(In reply to Xavier Van Wijmeersch from comment #19)
> found out countif(A1:B3;"") is not working nor with F9 or hard calculation
> but with countif(A1:B3;"<>=") then i have the correct value 6 without
> recalculation

Is that with a new (blank) spreadsheet, that COUNTIF(A1:B3;"") returns 3?
Can you upload or email that document?
Comment 21 Xavier Van Wijmeersch 2018-06-12 14:19:25 UTC
Created attachment 142680 [details]
countif issue

@Winfried

Its a new fresh spreadsheet
Comment 22 Winfried Donkers 2018-06-13 05:36:28 UTC
(In reply to Xavier Van Wijmeersch from comment #21)
> Created attachment 142680 [details]
> countif issue
> 
> @Winfried
> 
> Its a new fresh spreadsheet

In your document the range A1:B3 contain 3 cells with content and 3 empty cells.
COUNTIF(A1:B3;"") counts alle cells in the range A1:B3 that conform to the condition "" (empty cell). The correct result is 3 for your document.

If you have COUNTIF(A1:B3;"") with A1:B3 being completely empty, the result _was_ 3 (incorrect) before the bug fix, and now is 6 (correct) in version 6.0.5 and higher.

See text in comment #0 , first 2 lines for the bug description.

@exceller : could you -if this is the case- change the status to VERIFIED/FIXED?
Comment 23 Xavier Van Wijmeersch 2018-06-13 06:37:53 UTC
@Winfried
What about your own comment15, also in comment1 same formula, where you say that

"Current master, built on Linux (64bit) produces 6 with COUNTIF(A1:B3;"").

Daily build of version 6.0.6.0.0+ (x64), build time 2018-06-12 02:02:13 on Windows10 produces 6 with COUNTIF(A1:B3;"").

Daily build of version 6.1.0.0beta1 (x64), build time 2018-06-12 04:32:21 on Windows10 produces 6 with COUNTIF(A1:B3;"").

Pre release version 6.0.5.1 on Windows10 produces 6 with COUNTIF(A1:B3;"")."

Sorry but its for me not clear why you say this, and my formula from scratch is not working because empty cells or not counted???
Comment 24 Xavier Van Wijmeersch 2018-06-13 06:50:14 UTC
I did misunderstand the description and the explanation off comment22

Silly me, will close it as verified and fixed
Comment 25 Winfried Donkers 2018-06-13 07:55:12 UTC
(In reply to Xavier Van Wijmeersch from comment #24)
> I did misunderstand the description and the explanation off comment22
> 
> Silly me, will close it as verified and fixed

No problem, better to be safe than sorry ;-)