#1

Using countif like this: COUNTIF($A$2:$A5,A6) to count the number of times the item has already appeared in a list.

It works for some values and fails for others because some of the cells have text in them that includes brackets and A3’s contents are treated as a regular expression.

I do not want to change the default behaviour so how can I annotate the formula to prevent A3’s content being treated (rather bizarrely) as a regular expression?

#2

To enable better understanding could you please give examples of cell content of cells in which your formula works and in which not?

#3

There exists no local setting, but only the global ones in Tools. What do you want to do? Why do you need to count the occurrences? There exists likely a total different way without COUNTIF for your goal. Other questions: Do you want to compare exact, whole cell content? Do you compare only texts, or numbers as well? Can you provide a tiny (!) example document?

#4

I think a formula like: =SUMPRODUCT(FIND(A6,$A$2:$A$5)) should work, because FIND() does not use regular expressions.
Also you can use SUBSTITUTE() function to replace in the second parameter of COUNTIF() the brackets for \brackets ([ -> \]), but maybe are needed several SUBSTITUTE nested, one for every open and close bracket.

#5

Since I often use similar expressions when sorting selections from data ranges by formulae, I was interested in the question though I didn’t actually encounter the problem as it was presented here. “Be prepared!” was the motto.

I attach a file dealing with the matter. It demonstrates a workaround generally useable with MATCH(), COUNTIF() and its relatives and it contains some experimental material concerning SEARCH(). You’re invited to have a look if interested.

Sorry! As a newby I am seemingly not allowed to attach files.
I uploaded the file therefore to my own webspace. Edited: David fixed the attachment issue. Thanks!]

Attached: RegexOffLocal002.ods

#6

Impressive file - thanks! :slight_smile: I uploaded/attached to your post, so you don’t need to use your own space. Feel free to roll-back/edit, though.

#7