+ Reply to Thread
Results 1 to 5 of 5

Countif with wildcards

  1. #1
    Registered User
    Join Date
    06-02-2010
    Location
    milton keynes
    MS-Off Ver
    Excel 2003
    Posts
    1

    Countif with wildcards

    hi there.

    i have a column of telephone numbers taken from a program we use which gives us a raw data.

    all i am trying to do for the last 2 hours is to do a count of all cells where the number is starting with "7".

    I used the formula: =COUNTIF(A1:B8,"7*")

    It just keeps coming with zero as the result. Not sure whether its to do with the formatting of the cells.

    PLEASE HELP!!!!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Countif with wildcards

    If you click on one of those cells that should be counted, what do you see in the formula bar?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-01-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Countif with wildcards

    This may not be the most sophisticated answer, but you could add a column somewhere (let's say column E) and in the first cell (E1) use the formula =LEFT(A1,1). Then copy that down. Then your count formula can simply be =COUNTIF(E1:E8,"7") or =COUNTIF(E:E,"7)

    Once you set that up and copy the LEFT() formula down to more rows than you will ever use, you can just hide the column with the first digits so you don't have to look at it.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countif with wildcards

    Wildcards don't work with numeric values, try using SUMPRODUCT and LEFT, i.e.

    =SUMPRODUCT((LEFT(A1:B8)="7")+0)

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Countif with wildcards

    Or just:

    =SUMPRODUCT(--(LEFT(H2:H4)="7"))

    copied down.. no helpers...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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