Ask Your Question
0

COUNTIF double plus not working

asked 2020-11-24 13:48:36 +0200

rslz gravatar image

updated 2020-11-24 18:40:09 +0200

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?

C:\fakepath\demo.ods

edit retag flag offensive close merge delete

Comments

Hello @rslz, Press edit below your question and use the paper clip to upload an anonymized copy of the file with the problem.

LeroyG gravatar imageLeroyG ( 2020-11-24 16:33:12 +0200 )edit

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)?

Opaque gravatar imageOpaque ( 2020-11-24 17:00:27 +0200 )edit

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.

rslz gravatar imagerslz ( 2020-11-24 18:41:19 +0200 )edit
1

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}")

Opaque gravatar imageOpaque ( 2020-11-24 18:56:34 +0200 )edit

New documents are set by default to Enable wildcards in formulas.

LeroyG gravatar imageLeroyG ( 2020-11-24 19:22:29 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2020-11-24 16:38:03 +0200

PKG gravatar image

Why is that and how can I fix it?

Tools →

image description

edit flag offensive delete link more

Comments

I didn't even know that these options were on a per-spreadsheet basis.

rslz gravatar imagerslz ( 2020-11-24 18:48:42 +0200 )edit

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(), ...

Lupp gravatar imageLupp ( 2020-11-24 19:47:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-24 13:48:36 +0200

Seen: 67 times

Last updated: Nov 24 '20