Count occurences based on first X characters in string of numbers

  • Hi all,

    I've hit a snag and cannot figure out a workaround.
    I am trying to search row by row for any number starting with 425 (the numbers will range from 4250 to 4259) and report a binary result.

    say the data looked like this:
    A B C D (the columns go out to IC,the rows to 24875!!)
    Row1: 0385 / Jack / 4259 / Phil

    i have been trying =if(countif(a1:e1),"425?"),1,0), however, it doesnt seem to recognize the wildcard. I have tried using numbers as text and numbers and i get the same result. since the dataset is so large, im trying to automate whatever i can without having to use a nested if(or( to find all the data semi-automatically. any thoughts?
    thanks,
    pete

  • Re: Unable to search numeric strings for numbers starting with xxx


    Hi pezhed618,
    Nice to meet you.
    It works to me.
    =SUMPRODUCT((LEFT(A1:E1,3)="425")*1)
    Regards, junho

  • Re: Count values based on first X characters in string of numbers


    Hello Pete,

    Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:

    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different



    [COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]

  • Re: Count values based on first X characters in string of numbers


    Thanks for your help Junho. I am not getting the expected result with your command (after manipulating it for my acutal data set).

    Here is a real sample of the data: forum.ozgrid.com/index.php?attachment/37243/

    Again, I am trying to report back 1 or 0 whether each row contains ANY cell beginning with 425* (4250-4529). My method described above only worked if the 425 was in column E, otherwise, it wouldnt see it. Im not entirely sure why.

    And sorry about the title AAE, thanks for fixing it. I know next this time.

  • Re: Count occurences based on first X characters in string of numbers


    UPDATE: I got it to work by chaning all my number strings to text string with =text(. i thought if i did a format change, it would do the same thing, but obiviously not. its not the prettiest work around, but it works. i will leave the thread if anyone can answer the orig question: is there way to use wildcards in numeric strings or create a forumla that searches for any number starting with xxx.

    thanks all.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!