At 19:42 19/12/2015 +0200, Hylton Conacher wrote:
Running :O 5.0.3.2 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.
=COUNTIF(Data.$C$4:C$492,$B17)
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?)
Or:
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.
Brian Barker
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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
