I have a worksheet that is using conditional formatting to show duplicate values within certain ranges. Some of these ranges will contain phone extensions that use * as one of the digits. This is causing problems with trying to use countif. Is there any way to tell excel to use the exact value within the cell as opposed to interpreting that * as a wildcard?
I suggest a swift kick to whoever is putting the * in your raw data because I couldnt find an easy way to deal with that.
If I remember correctly I ended up processing the data before the countif so I could get around the issue.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
The easy answer is to use SUMPRODUCT instead as it doesn't support the use of wildcards.
The difficult answer is the tilde character ~ tells Excel that the next character should be evaluated as the literal character and not a wildcard ~*.
However, you haven't provided enough detailed info to make a more specific suggestion wrt using COUNTIF.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
That implies that the * is in the cell with the criteria, in which case, I think this method should work.Originally Posted by hullmo
=COUNTIF(range,SUBSTITUTE(criteria,"*","~*"))
I know this thread is 5 years old, but I don't know if it would be a copy to create a new thread.
I tried the solution of jason.b75 as conditional formatting rule, but for me it doesn't work.
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:
I get an error:
Please Login or Register to view this content.
Formula or value in conditional formatting is invalid
This works but uses wildcards (what I don't want):
Please Login or Register to view this content.
Last edited by mona92; 05-11-2021 at 02:37 AM.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks