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

1. ## 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  Reply With Quote

2. 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));"")`  Reply With Quote

3. 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. Originally Posted by Chi 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  Reply With Quote

4. @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.  Reply With Quote

5. @navic - The formulas you provided produced the results I wanted. Thank you very much for your time. Much appreciated!  Reply With Quote

6. @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.  Reply With Quote

7. Originally Posted by Chi Thank you very much for your time.
You're welcome.  Reply With Quote

#### Posting Permissions

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