+ Reply to Thread
Results 1 to 8 of 8

Wildcards?

  1. #1
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Wildcards?

    Hi Excel Users,

    This kind goes along the lines of my previous post, "Count If & Contains", where I have a column full of information, then seeking something specific within that column, & having Excel return a result of either the total number of occurrences or the sum of these results.

    I know in the past, I could use some type of wildcard, such as "*", which would find whatever I'm looking for, between the '*'s', then give me desired result from a count, countif, or sumif formula.

    For some reason, this doesn't seem to be working quite well because now, I'm getting a result of 0. This tells me that the formula is working, but something in my formula is not correct.

    Even though I don't have any formulas here, perhaps, some of the users, know how to create a forumal that woudl work. If need be, I'll submit an example file later.

    Thanks
    MyCon
    -- Using Latest Version of Excel

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Wildcards?

    Best to post the formula you tried

    Is the data text because wildcards don't work well with numbers.....

  3. #3
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Wildcards?

    Hi Excel Users,

    I found a formula that works from: http://www.mrexcel.com/archive/Formulas/11675.html


    =SUMPRODUCT((ISNUMBER(SEARCH(C1,$A$1:$A$3)))*($B$1:$B$3))

    This also works:
    =SUMPRODUCT((ISNUMBER(SEARCH("TEXT",$A$1:$A$3)))*($B$1:$B$3))


    It's not the formula or wildcard that I remember using before, but whatever works - works...

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Wildcards?

    EDIT: below is simply a reiteration / embellished version of dll's prior post...

    In relation the previously referred to thread: http://www.excelforum.com/excel-work...-contains.html

    I suspect the issue is because by XXXX you are actually referring to other digits rather than alpha characters... ie each entry in the range is a number, correct ?
    (on the above thread I had presumed (incorrectly) otherwise so apologies on my part for that)

    If that's the case then searching for a text string within a range of numbers will always return 0, eg:

    A1:A4

    1234
    4123
    1231
    2231
    Please Login or Register  to view this content.
    will return 0 given all of the above values are in fact numbers and not strings whereas the SUMPRODUCT approach:

    Please Login or Register  to view this content.
    works and returns 3 because it evaluates each entry as a string by means of the SEARCH function.

    So in essence it's an issue of data type when it comes to the COUNTIF/SUMIF approaches... your test should use an operator appropriate to the data type of the criteria range, eg:

    Please Login or Register  to view this content.
    If per your earlier thread you were concerned only with the three left most digits and your numbers were always of a consistent length (key) as per the example here - you could use two COUNTIF functions (or one COUNTIFS if using XL2007) eg:

    Please Login or Register  to view this content.
    in these instances however I would say that you're best served using the SUMPRODUCT as it's far more flexible (if potentially more "expensive")...

    If performance is a real concern I would say that you would then be best served creating an adjacent column to your numbers that converts them to strings, eg:

    Please Login or Register  to view this content.
    at which point

    Please Login or Register  to view this content.
    etc will work irrespective of "number" length etc etc... ie you can use SUMIF/COUNTIF with wildcards without issue.

    Does that help clarify things ?
    Last edited by DonkeyOte; 01-18-2010 at 04:02 AM.

  5. #5
    Registered User
    Join Date
    05-18-2010
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Wildcards?

    I have a similar question with a different flavor. I have a list of clients with account date opened in column C, date closed in D, and store number in G. Some managers run two stores and the manager is defined by the last 4 digits of the 5 digit store number.

    So, I want to know number of accounts opened per manager.

    COUNTIFS(ACCOUNTS!D:D,">0",ACCOUNTS!G:G,"*0701") doesn't work since the store number is a number but sumproduct doesn't work because I really need the multiple conditions...unless I'm missing something. If I only want one of the store numbers, I can put 10701 in the string and I get the right number back.

    Any insight would be appreciated. Thanks, Kim

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Wildcards?

    Try SUMPRODUCT like this

    =SUMPRODUCT((ACCOUNTS!D1:D1000>0)*(RIGHT(ACCOUNTS!G1:G1000,4)="0701"))

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Wildcards?

    k.compton, welcome to the board.

    If you have a question please ask it in your own thread - if you feel another thread is particularly relevant provide a link to it from your own.

  8. #8
    Registered User
    Join Date
    05-18-2010
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Wildcards?

    Thank you both - the formula works and I understand how you got there. I will post questions to new threads going forward. Have a great day!

+ 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