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?
Hello @rslz, Press edit below your question and use the paper clip to upload an anonymized copy of the file with the problem.
copied the cells to a new spreadsheet and the problem fixed itself?! Could this be related to a formatting issue?
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 formulas enabled 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.
Why is that and how can I fix it?
Tools →
I didn’t even know that these options were on a per-spreadsheet basis.
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(), …