Results 1 to 7 of 7

Thread: Formula to count the number of times a number appears in a row within in a text cell

  1. #1
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013

    Formula to count the number of times a number appears in a row within in a text cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I've attached a sample data, on the 'Data' tab, which shows in each cell a person's name, in short form or code, and a number, in bold, which denotes a placing number for each pairing of names.

    Beside each pairing, highlighted in pink, shown on the 'Pairs' tab, I wanted a formula to show the total number of times that they came 1=1st, 2=2nd, 3=3rd only, ignoring the 0=no placings.

    Just so you know, each person's name for each placing is listed with their own unique code on the 'Codes' tab which I would like to be incorporated in the formula. The reason being is that the listed names on the attached sample data only shows a small list of names and the formula(s) will eventually be used in a data with a vast number of names being approximately over 300 names.

    So I guess using Countif formula would not be suitable for this vast data.

    I hope this all makes sense and let me know if you have any queries about the attached data.

    Kind regards
    Chi
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Try on 'Pairings' sheet
    In A2 formula below
    Code:
    =SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;3;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;4;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;5;FALSE)))
    In C2 Array formula formula below
    Code:
    =IF(ROWS(Data!A$2:A2)>Codes!$D$1*COUNTIF(Data!$A$2:$A$100;"="&"?*");"";INDEX(Data!$A$2:$A$100;SMALL(IF(Data!$A$2:$A$100<>"";ROW(Data!$A$2:$A$100)-ROW(Data!$A$2)+1);MOD(ROWS(Data!A$2:A2)-1;COUNTIF(Data!$A$2:$A$100;"="&"?*"))+1)))
    In D2 formula below
    Code:
    =IFERROR(INDEX(Codes!$C$2:$C$29;INT((ROW(Data!A1)+4)/COUNTIF(Data!$A$1:$A$100;"*?")));"")
    I do not understand the logic of calculation in column B (%)?

    Try on 'Codes' sheet
    There are 'helper' columns on 'Codes' worksheet

    In C2 Array formula formula below
    Code:
    =IFERROR(INDEX($B$2:$B$29;MATCH(0;INDEX(COUNTIF($B$2:$B$29;"<"&$B$2:$B$29)-SUMPRODUCT(COUNTIF($B$2:$B$29;C$1:C1)););0));"")
    In D1 formula below
    Code:
    =COUNTA(C2:C29)-COUNTBLANK(C2:C29)
    In D2 Array formula formula below (copy across)
    Code:
    =IFERROR(INDEX($A$2:$A$29;MATCH(0;COUNTIF($C2:C2;$A$2:$A$29)+IF($B$2:$B$29<>$C2;1;0);0));"")
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    29
    Articles
    0
    Excel Version
    2016
    In "Pairings" tab,

    H2=SUMPRODUCT((Data!$A$2:$A$6=C2)*(ISNUMBER(FIND(LEFT(D2,2)&" "&RIGHT(D2,1),Data!$B$2:$O$6)))*(RIGHT(Data!$B$2:$O$6,1)-0>=1)*1) copy down, the result matches current result in column A.







    Quote Originally Posted by Chi View Post
    Hi

    I've attached a sample data, on the 'Data' tab, which shows in each cell a person's name, in short form or code, and a number, in bold, which denotes a placing number for each pairing of names.

    Beside each pairing, highlighted in pink, shown on the 'Pairs' tab, I wanted a formula to show the total number of times that they came 1=1st, 2=2nd, 3=3rd only, ignoring the 0=no placings.

    Just so you know, each person's name for each placing is listed with their own unique code on the 'Codes' tab which I would like to be incorporated in the formula. The reason being is that the listed names on the attached sample data only shows a small list of names and the formula(s) will eventually be used in a data with a vast number of names being approximately over 300 names.

    So I guess using Countif formula would not be suitable for this vast data.

    I hope this all makes sense and let me know if you have any queries about the attached data.

    Kind regards
    Chi

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,679
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @caabyyc
    Please don't quote whole posts -- it's just clutter and makes the thread hard to read. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.
    Thank you Ken for this secure forum.

  5. #5
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    @navic - The formulas you provided produced the results I wanted. Thank you very much for your time. Much appreciated!

  6. #6
    Seeker Chi's Avatar
    Join Date
    Jun 2015
    Posts
    17
    Articles
    0
    Excel Version
    excel 2013
    @caabyyc - Unfortunately, the formula you provided only looks at the data and pairing of names. In order to create the results I needed you would have to factor in the short codes on the 'Codes' tab but you didn't include this in your formula.

    However, I thank you for taking the time to look into this.

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Chi View Post
    Thank you very much for your time.
    You're welcome.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •