+ 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 - RIP 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
    29,464

    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

    RIP - d. 06/10/2022

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: CountIF and Wildcards

    Try this: =countif(A1:A100, "*" & B1 & "*")
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Expert 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