COUNTIF ignore wildcards

[mona92]

I have nearly the same problem like in this thread and tried its solution:

Don’t use * as a wildcard

I have a countif statement that I use to find all exam grades that are A*. I store A* in a cell and use that cell in the countif. Problem is it picks up everything starting with A as it’s treating the * as a wildcard. Is there a way round this?

www.mrexcel.com

I have two Sheets. On the first sheet ("Query" )I have a column where I want to format a word if this word exists anywhere on the second sheet ("Synonyme").
My formula looks like:

=COUNTIF(Synonyme!A1:AM146;SUBSTITUTE(B2,"*","~*"))>0

I get an error:

Formula or value in conditional formatting is invalid

This works but uses wildcards (what I don't want):

=COUNTIF(Synonyme!A1:AM146;B2)>0

 

[maabadi]

Welcome to MrExcel Message Board.
Are you try with Comma Not SemiColon at formula?

 

[mona92]

Welcome to MrExcel Message Board.
Are you try with Comma Not SemiColon at formula?

Yes, I also tried this. But same error.

 

[maabadi]

Are you have conditional formatting or data validation at that range or formula Cell?

 

[RoryA]

It appears you use semicolons as separators, so you need those in the SUBSTITUTE part too:

=COUNTIF(Synonyme!A1:AM146;SUBSTITUTE(B2;"*";"~*"))>0

 

[mona92]

I use conditional formatting and then the option "Use a formula to determine which cells to format". Is this what you asked for?

 

[mona92]

It appears you use semicolons as separators, so you need those in the SUBSTITUTE part too:

=COUNTIF(Synonyme!A1:AM146;SUBSTITUTE(B2;"*";"~*"))>0

Aaaah thank you so much!

 

[mona92]

But I still have something wrong. I need to check each row of column B. So I have to change the value in substitute of B2 to $B$2 or something like this?

 

[RoryA]

No, but you should make the first range absolute since you don't want that to change for each row:

=COUNTIF(Synonyme!$A$1:$AM$146;SUBSTITUTE(B2;"*";"~*"))>0