+ Reply to Thread
Results 1 to 6 of 6

Ignoring Wildcards in Countif

  1. #1
    Registered User
    Join Date
    04-12-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    5

    Ignoring Wildcards in Countif

    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?

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: Ignoring Wildcards in Countif

    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.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Ignoring Wildcards in Countif

    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.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Ignoring Wildcards in Countif

    Quote Originally Posted by hullmo View Post
    Is there any way to tell excel to use the exact value within the cell as opposed to interpreting that * as a wildcard?
    That implies that the * is in the cell with the criteria, in which case, I think this method should work.

    =COUNTIF(range,SUBSTITUTE(criteria,"*","~*"))

  5. #5
    Registered User
    Join Date
    05-11-2021
    Location
    Germany
    MS-Off Ver
    365
    Posts
    1

    Re: Ignoring Wildcards in Countif

    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:

    Please Login or Register  to view this content.
    I get an error:

    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.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,174

    Re: Ignoring Wildcards in Countif

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. CountIF and Wildcards
    By Justmegan93 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2014, 09:05 AM
  2. [SOLVED] Writing out a list of wildcards in a formula? involves COUNTIF
    By jasonbwt in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-26-2012, 09:42 AM
  3. [SOLVED] COUNTIF Using Wildcards
    By JoeJaycee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2012, 02:52 PM
  4. Replies: 3
    Last Post: 10-18-2010, 11:09 AM
  5. Countif with wildcards
    By seanjoe in forum Excel General
    Replies: 4
    Last Post: 06-02-2010, 09:04 AM
  6. is it possible to use countif with a cell ref and wildcards?
    By videot96 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-25-2009, 05:10 PM
  7. countif wildcards?
    By Ltat42a in forum Excel General
    Replies: 3
    Last Post: 12-18-2005, 12:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1