I have a column containing soft evaluations of the form "--", "-", "o", "+" and "++". When I try to use COUNTIF to count the number of occurrences it works, except for "++", which always returns zero. Why is that and how can I fix it?
EDIT: I copied the cells to a new spreadsheet and the problem fixed itself?! Could this be related to a formatting issue?
Why is that and how can I fix it?
Tools →
I didn't even know that these options were on a per-spreadsheet basis.
It's per document.
If you want to NOT disable RegEx evaluation in formulas for all the document, but to evaluate something like the COUNTIF without RegExy in a specific place, you can use {=COUNTIF(A$1:A$21="++"; TRUE())} (e.g.) where the curly brackets must not be entered, but show that the formula was entered for array-evaluation by Ctrl+Shift+Enter.
A better way, not needing explicitly forced array-evaluation would be =SUMPRODUCT(A$1:A$21="++"). The parameters of SUMPRODUCT() are specified ForceArray anyway.
In similar ways you can "suppress" the generally enabled RegEx functionality also for SEARCH(), MATC(), ...
Asked: 2020-11-24 13:48:36 +0100
Seen: 54 times
Last updated: Nov 24 '20
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
Hello @rslz, Press edit below your question and use the paper clip to upload an anonymized copy of the file with the problem.
The next guesswork - could you please add any details (formulas, formats being used, sample file)?
I uploaded the file. You can try yourself copy-pasting the cells into a new spreadsheet and it should magically work all of a sudden.
If you have
[o] Enable regular expressions in formulasenabled you need to use=COUNTIF(A$1:A$21; "\+\+")-- or -=COUNTIF(A$1:A$21; "\+{2}")New documents are set by default to Enable wildcards in formulas.