+ Reply to Thread
Results 1 to 6 of 6

CountIF and Wildcards

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    CountIF and Wildcards

    If I were to write
    =COUNTIF(A1:A100,"*123*")
    It would only count if cells A1 to A100 contained 123

    But if 123 was stored in cell B1 how would I write it ?

    for example
    =COUNTIF(A1:A100,*B1*)
    but I know this doesnt work

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: CountIF and Wildcards

    Try this

    =COUNTIF(A1:A100,"*"&B1&"*")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: CountIF and Wildcards

    Hi,

    =COUNTIF(A1:A100,"*"&B1&"*")

    will work.

    Incidentally with your original you wouldn't pick up a value which was a numeric 123. You'd need

    =COUNTIF(A1:A100,"*123")+COUNTIF(A1:A100,123)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,422

    Re: CountIF and Wildcards

    Try this: =countif(A1:A100, "*" & B1 & "*")

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: CountIF and Wildcards

    just
    =COUNTIF(A1:A100,"*"&B1&"*")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Scotland
    MS-Off Ver
    10
    Posts
    37

    Re: CountIF and Wildcards

    perfect

    Thanks x

+ 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. [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
  2. [SOLVED] COUNTIF Using Wildcards
    By JoeJaycee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2012, 02:52 PM
  3. Countif with wildcards
    By seanjoe in forum Excel General
    Replies: 4
    Last Post: 06-02-2010, 09:04 AM
  4. 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
  5. countif wildcards?
    By Ltat42a in forum Excel General
    Replies: 3
    Last Post: 12-18-2005, 12:59 PM

Tags for this Thread

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