Bug 114660 - COUNTIFS FUNCTION requires a reference to the inclusion of regular expressions in formulas
Summary: COUNTIFS FUNCTION requires a reference to the inclusion of regular expression...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium trivial
Assignee: sdc.blanco
URL:
Whiteboard: target:7.1.0
Keywords: easyHack
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
 
Reported: 2017-12-23 01:57 UTC by David Alastair Jones
Modified: 2020-11-19 23:28 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description David Alastair Jones 2017-12-23 01:57:11 UTC
Description:
When I follow the suggested syntax, I get errors [including Err 507, which is not even listed in the error messages.] Libre suggests corrections. These suggested corrections DO NOT follow the suggested syntax, and they also produce errors. There is a bad disconnect between the documented / suggested syntax and whatever actual syntax Libre is looking for.

Actual Results:  
When I follow the suggested syntax, I get errors [including Err 507, which is not even listed in the error messages.] Libre suggests corrections. These suggested corrections DO NOT follow the suggested syntax, and they also produce errors. There is a bad disconnect between the documented / suggested syntax and whatever actual syntax Libre is looking for.

Expected Results:
noted above


Reproducible: Always


User Profile Reset: No



Additional Info:
1. Syntax as per the Help and syntax within Libre should be consistent. 2. When Libre suggests an amendment to my attempt, that suggestion should be consistent with the syntax as set out in help. A major discrepancy I note is that the Help wants a ";" between parameters, whereas the suggested pollution that Libre produces uses a "," between parameters.


User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:57.0) Gecko/20100101 Firefox/57.0
Comment 1 MM 2017-12-23 09:45:11 UTC
Unconfirmed on windows 7 x64 with Version: 6.0.0.1
Build ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
CPU threads: 3; OS: Windows 6.1; UI render: default; 

I get the normal outcome instead of err507.
Only with =COUNTIFS(A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) I get 3 as outcome instead of 1.
Comment 2 tagezi 2017-12-26 18:08:44 UTC
(In reply to David Alastair Jones from comment #0)
everything works correctly in master.
attach a document with an example, please
Comment 3 tagezi 2017-12-26 18:11:38 UTC
(In reply to MM from comment #1)
> Unconfirmed on windows 7 x64 with Version: 6.0.0.1
> Build ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
> CPU threads: 3; OS: Windows 6.1; UI render: default; 
> 
> I get the normal outcome instead of err507.
> Only with =COUNTIFS(A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) I get 3 as outcome
> instead of 1.

You forgot to enter a pen in the E2 cell.
And it seems, the behavior of regex has been changed.
Comment 4 tagezi 2017-12-26 18:29:26 UTC
Tools -> Options... LibreOffice Calc -> Enable regular expressions in formulas

source/text/scalc/01/func_countifs.xhp
Comment 5 MM 2018-01-06 01:34:15 UTC
(In reply to tagezi from comment #3)
> (In reply to MM from comment #1)
> > Unconfirmed on windows 7 x64 with Version: 6.0.0.1
> > Build ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
> > CPU threads: 3; OS: Windows 6.1; UI render: default; 
> > 
> > I get the normal outcome instead of err507.
> > Only with =COUNTIFS(A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6)) I get 3 as outcome
> > instead of 1.
> 
> You forgot to enter a pen in the E2 cell.
> And it seems, the behavior of regex has been changed.

Thanks for pointing that out, now it reads 0 instead of 1. Still no err 507 though.
Comment 6 Commit Notification 2020-11-19 23:22:14 UTC
Seth Chaiklin committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/4e4118d7e8b08b9ea42e37f75f7ef168bdab0745

tdf#114660 add explanation about setting regex option in Calc help example
Comment 7 sdc.blanco 2020-11-19 23:28:31 UTC
Confirm that all examples in: 
https://help.libreoffice.org/7.1/en-US/text/scalc/01/func_countifs.html?&DbPAR=WRITER&System=UNIX

give the results as described on that page, when using 7.1.0.0.alpha1+

Have added a paragraph to the section of examples using regular expressions, to indicate that it is necessary to enable regular expressions in formulas in the Options.  Closing as FIXED.