Created attachment 123101 [details] Simple Array Formula This simple Array Formula evaluates to 4, when the correct result should be 3. Excel evaluates to 3. The example came from the tutorial, here: http://www.myonlinetraininghub.com/excel-search-and-you-will-find
I have not excel, maybe SEARCH() has a different return when text it's not founded, in calc returns #VALUE!. {=COUNT(IF(ISERROR(SEARCH("brown";A1:A4));"";1))} or {=COUNT(IFERROR(SEARCH("brown";A1:A4);""))} or =SUMPRODUCT(NOT(ISERROR(SEARCH("brown";A1:A4)))) or =SUMPRODUCT(IFERROR(SEARCH("brown";A1:A4)>0;0)) or {=COUNT(IFERROR(SEARCH("brown";A1:A4)>0;""))} works for me, the interest of SUMPRODUCT() it's that is an implicit array, avoiding the need to enter the formula as array
Eike, Our functions should follow the same specification as Excel, right?
Excel function COUNT ignore errors values while Calc COUNT propagates errors. See Excel COUNT : https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c Not related to this bug, but function LOOKUP also ignore errors in Excel.
Actually Calc also ignores errors in COUNT, just not in array mode ... to be fixed. @Gerard: What makes you think that LOOKUP ignores errors?
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b2f5336b08b5f638f890a626eb2aeefaf499a79b Resolves: tdf#98297 exclude error values from COUNT in array/matrix It will be available in 5.2.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.
Pending review https://gerrit.libreoffice.org/22843 for 5-1 https://gerrit.libreoffice.org/22846 for 5-0
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6e7e8af8ed2ee873a5c8a8b7b42fe3b99797be90 unit test for tdf#98297, exclude/include error values with COUNT/COUNTA It will be available in 5.2.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.
The Array Formulas with count in the tutorial are all working now. Thanks Eike!
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f718d779a99e86dc43444338d48c89cf64e7f9e&h=libreoffice-5-1 Resolves: tdf#98297 exclude error values from COUNT in array/matrix It will be available in 5.1.2. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=84f8fa501ac772b40639d7b6e95ebeb752b01bf5&h=libreoffice-5-0 Resolves: tdf#98297 exclude error values from COUNT in array/matrix It will be available in 5.0.6. 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.
(In reply to Eike Rathke from comment #4) > @Gerard: > What makes you think that LOOKUP ignores errors? Sorry to reply in a fixed bug, but this is the answer to your question : https://bugs.documentfoundation.org/show_bug.cgi?id=100266