At 19:42 19/12/2015 +0200, Hylton Conacher wrote:
Running :O 184.108.40.206 on IMac and have seen there
is a 5.0.4 update, however, perhaps my issue does not require an update.
Anyway, the formula involves COUNTIF with the
search range on another sheet and the search
criteria in a cell adjacent to the countif formula.
Each search criteria (B17), is text consisting
of spaces, numbers and special characters [e.g.] BLUE GUM LANE (NO'S 1 - 17)
On my version of LO this formula displays a zero
as the count of the search criteria however on
reviewing the Data sheet, I can identify at
least one exact replica within range, yet it is
not counted? Any ideas to get the correct answer
of the number of times a field appears?
There are simple ways to debug problems such as
this - which you can do with your actual
spreadsheet but you deprive anyone offering to help you from doing!
o Set up a model spreadsheet with very simple
entries so that something works - to establish
that you understand exactly what COUNTIF() does.
o Make a copy of your real spreadsheet and
simplify it - removing material and simplifying
the data - until it works. Whatever you changed
last to make the function spring into life will be the culprit.
As the help text explains about COUNTIF(), "[t]he
search supports regular expressions." With
regular expression enabled, the parentheses in
your example data are interpreted as marking off
the enclosed text as a reference. This can be
referred to later in a search string (or in a
replacement string, where relevant). This means
that the parentheses are punctuation within the
search string and not part of it: your example
string would match "BLUE GUM LANE NO'S 1 - 17"
but not "BLUE GUM LANE (NO'S 1 17)".
If this is the problem, there are two alternative
techniques to solve it. Either:
o Disable regular expressions in formulae by
removing the tick from Tools | Options... |
LibreOffice Calc | Calculate | Enable regular
expressions in formulae. (That will be Preferences... on the Mac, won't it?)
o Escape the parentheses by preceding them by
backslashes in the search string: BLUE GUM LANE
\(NO'S 1 - 17\) - so that they are treated as
nothing more than the simple text characters you mean them to be.
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy