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
Created attachment 141905 [details] Count.If from Excel 2010
Created attachment 141906 [details] Same file writed with Calc
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.
Seems only works for those formulas with the first column, but works searching for other values.
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
I will have a look at it to see if I can find the cause.
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.
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.
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.
*** Bug 118014 has been marked as a duplicate of this bug. ***
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
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.
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
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...
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?
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.
(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.
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
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
(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?
Created attachment 142680 [details] countif issue @Winfried Its a new fresh spreadsheet
(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?
@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???
I did misunderstand the description and the explanation off comment22 Silly me, will close it as verified and fixed
(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 ;-)